mysql - 将行分组到多列中并进行计数

Question

我有一个包含这些数据的表格:

SELECT typeId, status FROM geo WHERE userId = 1 LIMIT 5;
+--------+--------+
| typeId | status |
+--------+--------+
|      2 | 2      |
|      2 | 5      |
|      8 | 2      |
|      3 | 3      |
|      2 | 5      |
+--------+--------+

我想计算每个 typeID 的不同状态的数量。这是预期的结果:

status 2 = active
status 3 = disabled
status 5 = archived

+--------+--------+----------+----------+
| typeId | active | disabled | archived |
+--------+--------+----------+----------+
|      2 | 1      | 0        | 2        |
|      8 | 1      | 0        | 0        |
|      3 | 0      | 1        | 0        |
+--------+--------+----------+----------+

0 can be is NULL, not a problem

我尝试使用这样的查询:“SELECT typeId，CASE WHEN status = 2 THEN status END AS active，CASE WHEN status = 3 THEN status END AS禁用，CASE WHEN status = 5 THEN status END AS archived FROM geo WHERE userId = 1 group by typeId"但当然这不是预期的结果..我得到了状态，而不是计数，我不知道如何计数它们。

感谢您的帮助!

Answer 1

您需要聚合函数:

SELECT typeId, SUM( status = 2 ) AS active, SUM( status = 3 ) AS disabled, SUM( status = 5 ) AS archived
FROM geo
WHERE userId = 1 
GROUP BY typeId;

请注意，这使用了一个方便的 MySQL 快捷方式，该快捷方式将 bool 值“当作”整数求和，其中 1 表示 true，0 表示 false。