将行的值计数作为列名mysql

Question

我有一个名为user_completed的表

CREATE TABLE IF NOT EXISTS `user_completed` (
  `rowId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `designer_id` int(10) unsigned NOT NULL,
  `status` varchar(54) DEFAULT NULL,
  PRIMARY KEY (`rowId`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;


INSERT INTO `user_completed` (`rowId`, `designer_id`, `status`) VALUES
(1, 1, accept),
(2, 1, reject),
(3, 1, accept),
(4, 1, reject),
(5, 1, overtime),
(6, 2, accept)
(7, 2, accept)
(8, 3, accept)
(9, 2, reject);

看起来像这样：

rowId   designer_id    status 
1           1          accept
2           1          reject
3           1          accept
4           1          reject
5           1          overtime
6           2          accept
7           2          accept
8           3          accept
9           2          reject

我希望得到以下结果：

designer_id   accept   overtime   reject
1             2        1          2
2             2        0          1
3             1        0          0

但是我不知道如何将designer_id分组，然后计算不同status的个数，并将每个结果放入上述列中。

- xCiCi

---

你可以在这里找到很多答案...https://dev59.com/EWsz5IYBdhLWcg3wxax3 - user13116294

Answer 1

试试这个

SELECT  designer_id,
    SUM(IF(status = 'accept',1,0)) as 'Accept',
    SUM(IF(status = 'reject',1,0)) as 'Reject',
    SUM(IF(status = 'overtime',1,0)) as 'Overtime'
FROM 
    user_completed
Group By designer_id

演示代码

正如Jack所说，这很简单，只需要这样做：

SELECT  designer_id,
    SUM(status = 'accept') as 'Accept',
    SUM(status = 'reject') as 'Reject',
    SUM(status = 'overtime') as 'Overtime'
FROM 
    user_completed
Group By designer_id

Fiddle Demo

- Vignesh Kumar A

Answer 2

尝试这个，它会起作用。

select designer_id, 
count(case status  when 'accept'then 1 else null end)as accept,
count(case status when 'reject'then 1 else null end)as reject,
count(case status when 'overtime'then 1 else null end)as overtime
from user_completed group by designer_id

- Mohini Mhetre

Answer 3

如果您不知道有多少个不同的状态，那么可以查看此链接以获取解决方案

将行动态转换为列的Mysql查询

例如，您可以使用以下内容：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
   CONCAT(
     'SUM(IF(`status` = "', `status`, '",1,0)) AS ', `status`)
  ) INTO @sql
FROM user_completed;

SET @sql = CONCAT('SELECT  designer_id, ', @sql, ' 
              FROM    user_completed
              GROUP  BY  designer_id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

- Tau