如何用SQL按部门统计员工出勤/缺勤/迟到人数

原始数据表 #

EmpId   section attn_status
101     Admin       P
102     Admin       P
103     Admin       L
104     Admin       A
105     Store       P
106     Store       L
107     Store       A
108     Security    P
109     Security    L
110     Security    P
111     Security    P

期望查询结果 #

section     Present Absent Late
Admin       2       1       1
Store       1       1       1
Security    3       0       1

实现SQL语句 #

SELECT 
    section,
    SUM(CASE WHEN attn_status = 'P' THEN 1 ELSE 0 END) AS Present,
    SUM(CASE WHEN attn_status = 'A' THEN 1 ELSE 0 END) AS Absent,
    SUM(CASE WHEN attn_status = 'L' THEN 1 ELSE 0 END) AS Late
FROM 
    your_table_name
GROUP BY 
    section
ORDER BY 
    section;

说明 #

• 用CASE语句判断attn_status的取值，匹配到对应状态时返回1，否则返回0
• 通过SUM函数对每个部门分组内的状态计数，无对应状态时自动统计为0
• GROUP BY section实现按部门分组聚合，ORDER BY section可让结果按部门名称排序（按需选择是否保留）

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