MYSQL GROUP BY/COUNT 带条件

Question

我希望扩展一个简单的“GROUP/COUNT BY”查询，其条件是根据条件仅获取一定数量的行。

这是一个简单的表格。普通的“GROUP BY”会计算并返回完整的行数，但我需要使用表旁边看到的语句来扩展我的查询:

+-----+----------+----------+-------+| id  | deleted  | status   |  ref  |+-----+----------+----------+-------+|   1 |        0 | paid     | 10001 ||   2 |        0 | paid     | 10001 ||   3 |        0 | paid     | 10001 |   WHERE status = paid ABD id IN (2,3) //COUNT = 2|   4 |        0 | paid     | 10002 ||   5 |        1 | cat      | 10002 ||   6 |        1 | cat      | 10002 |   WHERE status = cat AND id IN (6) //COUNT = 1|   7 |        0 | cat      | 10003 ||  12 |        0 | pending  | 10004 ||  13 |        1 | pending  | 10005 ||  14 |        1 | pending  | 10005 ||  15 |        1 | pending  | 10005 |   WHERE status = pending AND id IN(12,14,17) COUNT = 3|  16 |        0 | pending  | 10005 ||  17 |        0 | pending  | 10006 ||  18 |        0 | pending  | 10006 ||  19 |        0 | pending  | 10006 |+-----+----------+----------+-------+

在上述条件下获得下面所期望的结果的最简单方法是什么？

+----------+---------+| status   |  COUNT  |+----------+---------+| paid     | 2       || cat      | 1       || pending  | 3       | +----------+---------+

Answer 1

我想知道如果您提前知道所有所需的行，为什么还要执行查询。无论如何，一个 where 条件 where id IN (2,3,6,12,14,17) 就可以完成这项工作。所以最终的查询是

select status, count(*)
from mySimpleTable
where id IN (2,3,6,12,14,17)
group by status