mysql - MySQL COUNT IF 与 GROUP BY

Question

我正在尝试解决这个 LeetCode 问题（https://leetcode.com/problems/get-highest-answer-rate-question/）：

我已经在survey_log本地生成了：

mysql> select * from survey_log;
+------+--------+-------------+-----------+-------+-----------+
| uid  | action | question_id | answer_id | q_num | timestamp |
+------+--------+-------------+-----------+-------+-----------+
|    5 | show   |         285 |      NULL |     1 |       123 |
|    5 | answer |         285 |    124124 |     1 |       124 |
|    5 | show   |         369 |      NULL |     2 |       125 |
|    5 | skip   |         369 |      NULL |     2 |       126 |
+------+--------+-------------+-----------+-------+-----------+

我想使用这个辅助表：

mysql> select question_id, if(action='show', 1, 0) as is_show, if(action='answer', 1, 0) as is_answer from survey_log;
+-------------+---------+-----------+
| question_id | is_show | is_answer |
+-------------+---------+-----------+
|         285 |       1 |         0 |
|         285 |       0 |         1 |
|         369 |       1 |         0 |
|         369 |       0 |         0 |
+-------------+---------+-----------+

接下来，我想做的是得到每个is_show和is_answer列的总和question_id。我认为这会起作用：

mysql> select question_id, count(if(action = 'show', 1, 0)) as show_count, count(if(action = 'answer', 1, 0)) as answer_count from survey_log group by question_id;
+-------------+------------+--------------+
| question_id | show_count | answer_count |
+-------------+------------+--------------+
|         285 |          2 |            2 |
|         369 |          2 |            2 |
+-------------+------------+--------------+

但是，这并没有给我预期的输出

+-------------+------------+--------------+
| question_id | show_count | answer_count |
+-------------+------------+--------------+
|         285 |          1 |            1 |
|         369 |          1 |            0 |
+-------------+------------+--------------+

最后一个查询有什么问题？我查看了https://dev.mysql.com/doc/refman/8.0/en/counting-rows.html但似乎无法将其应用于此问题。

标签： mysqlcount

Answer 1

COUNT只计算非空值。所以两者0都会1被计算在内。你需要做的是SUM他们：

select question_id, 
       sum(if(action = 'show', 1, 0)) as show_count,
       sum(if(action = 'answer', 1, 0)) as answer_count
from survey_log
group by question_id;

请注意，由于 MySQL 在数字上下文中将布尔值视为 0 (false) 或 1 (true)，因此您可以删除if查询的部分：

select question_id, 
       sum(action = 'show') as show_count,
       sum(action = 'answer') as answer_count
from survey_log
group by question_id;