php - mysql单个查询对多个值进行计数

Question

我需要单个查询来计算多个状态值...

表格

+-------------+------------------+--------+
| ID          | Name             | Status |
+-------------+------------------+--------+
| 50          | Name1            | 1      |
| 49          | Name2            | 2      |
| 50          | Name1            | 1      |
| 49          | Name2            | 1      |
| 50          | Name1            | 2      |
| 50          | Name1            | 2      |
| 50          | Name1            | 3      |
| 50          | Name1            | 3      |
| 50          | Name1            | 1      |
+-------------+------------------+--------+

预期输出:

+-------------+------------------+--------------+--------------+--------------+
| ID          | Name             | Cnt(Status1) | Cnt(Status2) | Cnt(Status3) |
+-------------+------------------+--------------+--------------+--------------+
| 50          | Name1            | 3            | 2            | 2            |
| 49          | Name2            | 1            | 1            | 0            |
+-------------+------------------+--------------+--------------+--------------+

我需要查询上述输出...

Answer 1

您可以使用条件求和来做到这一点

select
id,
Name,
sum(Status=1) as cnt_status1,
sum(Status=2) as cnt_status2,
sum(Status=3) as cnt_status3
from table_name
group by id,Name