如何统计各用户不同status_id的频次？SQL行列转换求助

数据表结构与数据 #

用户表（masterdb.auth_user） #

ID         Username
1           Dan
2           Eli
3           Sean
4           John

状态记录表（masterdb.xmlform_joblist） #

user_id           Status_id
    1                   2  
    1                   3
    4                   1
    3                   2
    2                   3
    1                   1
    3                   3
    3                   3
    3                   3
    .                   .
    goes on             goes on

预期查询结果 #

username     status_id(1)   status_id(2)     status_id(3)
Dan                1             1                1
Eli                0             0                1
Sean               0             1                2
John               1             0                0 

当前SQL问题 #

现有代码仅能得到各status_id的单独计数，无法将不同状态拆分为独立列展示：

SELECT b.username , COUNT(a.status_id)
FROM masterdb.auth_user b
left outer join masterdb.xmlform_joblist a
on a.user1_id = b.id 
GROUP BY b.username, b.id, a.status_id

解决方案 #

使用条件聚合实现行列转换，将每个status_id的计数转为单独列：

SELECT 
    b.username,
    SUM(CASE WHEN a.status_id = 1 THEN 1 ELSE 0 END) AS `status_id(1)`,
    SUM(CASE WHEN a.status_id = 2 THEN 1 ELSE 0 END) AS `status_id(2)`,
    SUM(CASE WHEN a.status_id = 3 THEN 1 ELSE 0 END) AS `status_id(3)`
FROM masterdb.auth_user b
LEFT JOIN masterdb.xmlform_joblist a ON a.user1_id = b.id
GROUP BY b.id, b.username

关键说明 #

• CASE语句针对每个状态ID做判断，匹配时记1，否则记0
• SUM函数汇总每个用户对应状态的总次数，LEFT JOIN确保所有用户都会被返回，无匹配状态时计数为0
• GROUP BY只需按用户ID和用户名分组，无需包含status_id

内容的提问来源于stack exchange，提问作者Flint_Lockwood