MySQL基于2列对结果进行计数和分组

Question

如果表中有以下数据答案:

+-------------------------------------------+
| forum_id | user_id | question_id | answer |
+-------------------------------------------+
| 1        | 1       | 1           | A      |
| 1        | 1       | 2           | B      |
| 1        | 2       | 1           | B      |
| 1        | 2       | 2           | B      |
| 1        | 3       | 1           | A      |
| 1        | 3       | 2           | B      |
| 2        | 1       | 1           | A      |
| 2        | 2       | 2           | A      |
+-------------------------------------------+

我如何选择答案计数？要重新运行结果，例如:

+------------------------------+
| question_id | answer | count |
+------------------------------+
| 1           | A      | 2     |
| 1           | B      | 1     |
| 2           | A      | 3     |
| 2           | B      | 0     |
+------------------------------+

这就是我现在所拥有的。我知道它必须使用 GROUP BY，但我正在努力返回预期结果。

SELECT question_id, answer, COUNT(*)
FROM answers
WHERE forum_id = 1
GROUP BY question_id, answer

Answer 1

试试这个:

SELECT t.question_id, t.answer, COUNT(DISTINCT user_id)
FROM
(
  SELECT DISTINCT a.question_id, t.answer
  FROM answers as a, (SELECT 'A' AS answer UNION ALL SELECT 'B') AS t
) AS t
LEFT JOIN answers  AS a ON a.question_id = t.question_id
AND a.answer = t.answer and forum_id = 1
GROUP BY t.question_id, t.answer;

• demo

结果:

| question_id | answer | count |
|-------------|--------|-------|
|           1 |      A |     2 |
|           1 |      B |     1 |
|           2 |      A |     0 |
|           2 |      B |     3 |

请注意:

在您的示例数据中，B 有 3 个问题 2 的条目，而 A 没有问题 2 的条目。当然，所有论坛 = 1，所以您的预期结果是不正确的，我认为我的结果是正确的。