SQL Server聚合查询：按员工统计工资与奖金总额

源数据示例 #

id          Name        PaymentType   Payment   
----        -----       ------        -------
1           John         Salary       100
2           Peter        Salary       100
3           John         Bonus        20
4           Russel       Salary       100
5           Bill         Salary       100
6           Bill         Bonus        40
7           John         Salary       100

期望输出 #

Name          Salary     Bonus
-------------------------------
John          200        20
Peter         100        0
Russel        100        0
Bill          100        40

实现SQL语句 #

假设表名为Payments，可以通过条件聚合实现需求：

SELECT 
    Name,
    SUM(CASE WHEN PaymentType = 'Salary' THEN Payment ELSE 0 END) AS Salary,
    SUM(CASE WHEN PaymentType = 'Bonus' THEN Payment ELSE 0 END) AS Bonus
FROM Payments
GROUP BY Name
ORDER BY Name;

语句说明 #

• GROUP BY Name：按员工姓名分组，保证每个员工仅返回一行统计结果
• SUM(CASE WHEN ...)：通过CASE语句筛选对应类型的支付金额，非目标类型则取0，再用SUM函数计算总额，确保无对应记录时显示0
• ORDER BY Name：按姓名排序，与示例输出顺序保持一致（可根据实际需求调整）

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