Oracle SQL实现：为兼具薪资与奖金的员工计算总薪酬

现有employee_paystubs表，需新增计算字段NEW_COL：仅当员工同时拥有薪资（SALARY）和奖金（BONUS）（即count(distinct(pay_type)) > 1）时，在对应行显示该员工的总薪酬总额，否则为空。

当前尝试的查询语句 #

Select check_num, emp_id, name, pay_type, sum(amount) as amt
from employee_paystubs
group by check_num, emp_id, name, pay_type
order by check_num desc;

当前查询返回结果 #

期望输出结果 #

可行的Oracle SQL解决方案 #

WITH emp_summary AS (
    SELECT 
        check_num, 
        emp_id, 
        name, 
        pay_type, 
        SUM(amount) AS amt,
        -- 计算员工所有薪酬记录的总额
        SUM(SUM(amount)) OVER (PARTITION BY emp_id) AS total_compensation,
        -- 统计员工拥有的不同薪资类型数量
        COUNT(DISTINCT pay_type) OVER (PARTITION BY emp_id) AS pay_type_count
    FROM employee_paystubs
    GROUP BY check_num, emp_id, name, pay_type
)
SELECT 
    check_num, 
    emp_id, 
    name, 
    pay_type, 
    amt,
    -- 仅在员工有多种薪资类型且当前行是奖金时显示总薪酬
    CASE 
        WHEN pay_type_count > 1 AND pay_type = 'BONUS' 
        THEN total_compensation 
        ELSE NULL 
    END AS NEW_COL
FROM emp_summary
ORDER BY check_num DESC;

逻辑说明 #

1. 通过CTEemp_summary，用窗口函数分别计算每个员工的总薪酬，以及该员工拥有的薪资类型数量；
2. 外层查询通过CASE语句筛选：只有当员工同时存在薪资和奖金，且当前行是奖金记录时，才在NEW_COL显示总薪酬，其余情况为空，完全匹配期望输出。

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