带有 GROUP BY 计数的 MySQL CASE

Question

我有这三个问题

SELECT COUNT(question_id) AS correct_count, uID FROM mytable  WHERE id
>= 0 AND id <= 1000 AND correct = 1 AND answer_id IS NOT NULL GROUP BY user_id


SELECT COUNT(question_id) AS incorrect_count, uID FROM mytable  WHERE id >= 0 AND id <= 1000 AND correct !=1 AND answer_id IS NOT NULL GROUP BY user_id

SELECT COUNT(question_id) AS null_count, uID FROM mytable  WHERE id >= 0 AND id <= 1000 AND answer_id IS NULL GROUP BY user_id

如何使用 CASE 或 IF 将其作为单个查询加入？

Answer 1

SELECT user_id, 
       sum(id >= 0 AND id <= 1000 AND correct = 1 AND answer_id IS NOT NULL) AS correctCnt, 
       sum(id >= 0 AND id <= 1000 AND correct !=1 AND answer_id IS NOT NULL) AS incorrectCnt,
       sum(id >= 0 AND id <= 1000 AND answer_id IS NULL) as nullCnt
FROM mytable  
GROUP BY user_id