mysql - MySQL group by 中不满足条件时显示计数 0

Question

示例记录:

examid      setcode     answer
-------     --------   --------
10            A           A
10            A           B
10            A           X
10            B           A
10            B           B
10            B           C

我正在尝试通过 setcode 查找计数组，其中答案为 X。我尝试过以下查询:

SELECT setCode,COUNT(answer) FROM mcq_answer WHERE examid=10 AND answer='X' GROUP BY setCode

这个返回以下结果:

setcode     count
-------     -------- 
A            1  

但我正在寻找以下内容:

setcode     count
-------    -------- 
  A           1 
  B           0 

这里的Setcode是动态的。我只提到了A和B。可能有更多的 setocdes，如 C、D、E、F 等。我该怎么办。我正在使用MySQL

Answer 1

您可以使用以下查询

SELECT  C.setCode ,
    SUM(CASE WHEN B.setcode IS NULL THEN 0
             ELSE 1
        END) AS answer
FROM    ( SELECT    A.setCode ,
                COUNT(1) AS cnt
      FROM      mcq_answer A
      GROUP BY  A.setCode
    ) C
    LEFT JOIN mcq_answer b ON C.setcode = B.setcode
                              AND B.answer = 'X'
 GROUP BY  C.setCode