mysql - 改进How to get the number of occurrence for each distinct Value of Group_concat Mysql

Question

我正试图摆弄这个答案以获得更好的输出。我有一张这样的 table

name |status
-------------
mike |yes
mike |yes
mike |no
mike |ney
john |no
john |ney
john |yes

输出这样的东西

name |status           |total
------------------------------
mike |yes-2,no-1,ney-1 | 4
john |yes-1,no-1,ney-1 | 3

有人提出了这个非常有效的答案。

SELECT name, GROUP_CONCAT(totalPerStatus) AS status, 
       (SELECT COUNT(*) FROM mytable WHERE name = t.name) AS total
FROM (
  SELECT name,      
         CONCAT(status, '-', COUNT(*)) AS totalPerStatus            
  FROM mytable
  GROUP BY name, status ) t
GROUP BY name;

但我想改进这个输出以获得类似的东西

name | yes | no | ney | total
------------------------------
mike |2    |1   |1    | 4
john |1    |1   |1    | 3

Answer 1

假设(在评论中确认)只有三种状态是yes、no和ney，最简单的可能是只是 count 条件 case 语句:

SELECT   name,
         COUNT(CASE status WHEN 'yes' THEN 1 ELSE NULL END) AS yes,
         COUNT(CASE status WHEN 'no' THEN 1 ELSE NULL END) AS no,
         COUNT(CASE status WHEN 'ney' THEN 1 ELSE NULL END) AS ney,
         COUNT(*) AS total
FROM     mytable
GROUP BY name