这是我的表格结构：
-- qanda (stands for questions and answers)
+----+---------+-----------------------------------------------+--------------+-----------+------+
| id | title | content | question_id | user_id | type |
+----+---------+-----------------------------------------------+--------------+-----------+------+
| 1 | title1 | this is a question | NULL | 213423 | 0 |
| 2 | NULL | this is an answer | 1 | 435344 | 1 |
| 3 | NULL | this is another answer | 1 | 432435 | 1 |
| 4 | title2 | this is another question | NULL | 124324 | 0 |
| 5 | NULL | this is an answer for the second question | 4 | 213423 | 1 |
| 6 | NULL | this is another answer for the first question | 1 | 213423 | 1 |
+----+---------+-----------------------------------------------+--------------+-----------+------+
我想分别计算问题和答案的数量。我该怎么做？
此用户的预期结果：
:user_id = 213423+--------+--------+
| q_num | a_num |
+--------+--------+
| 1 | 2 |
+--------+--------+
我可以分别通过两个查询来实现这一点：
SELECT count(*) q_num FROM qanda WHERE user_id = :user_id AND question_id IS NULL
SELECT count(*) a_num FROM qanda WHERE user_id = :user_id AND question_id IS NOT NULL
我能在一个查询中做到吗？
最佳答案
你可以这样做：
SELECT count(questionid) as q_num,
sum(questionid is null) as a_num
FROM qanda
WHERE user_id = :user_id ;
count()使用列或表达式计算非-NULL值的数量——这正是您想要做的。MySQL将布尔值视为数字上下文中的整数，1表示真，0表示假。你也可以这样写：
(count(*) - count(questionid)) as a_num
或
sum(case when questionid is null then 1 else 0 end) as a_num
编辑：
使用类型，可以使用变量：
select sum(type = 0) as q_num, sum(type = 1) as a_num
关于mysql - 如何分别计算匹配的行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45673056/