查询指定日期范围内员工最新薪资及奖金总额的SQL优化问题

数据表结构 #

table employee {
    id,
    name
}

table payment_record {
   id,
   type, -- 1为薪资，2-4为奖金
   employee_id,
   date_paid,
   amount
}

需求说明 #

查询指定日期范围内员工的两项数据：

• 最新薪资：仅统计type=1的记录，取支付日期最新的那条薪资金额
• 奖金总额：统计type=2/3/4的所有记录金额之和

示例数据 #

payment_record表示例数据：

id, type, employee_id, date_paid, amount
1      1            1  2022-10-01   5000
2      2            1  2022-10-01   1000
3      3            1  2022-10-01   1000     
4      1            1  2022-11-01   3000
5      1            2  2022-10-01   1000
6      1            2  2022-11-01   2000
7      2            2  2022-11-01   3000

期望输出 #

查询日期范围['2022-10-01', '2022-11-01']的期望结果：

employee_id,  employee_name,  newest_salary, sum(bonus)
1             Jeff            3000           2000
2             Alex            2000           3000

注：原示例中员工2的最新薪资应为2000，与数据记录一致

当前SQL的问题 #

原SQL中用Max(case when type = '1' then amount end)获取薪资，逻辑错误：该语句取的是薪资金额的最大值，而非支付日期最新的薪资金额。

修正后的SQL #

SELECT
    emp.id AS employee_id,
    emp.name AS employee_name,
    COALESCE(s.newest_salary, 0) AS newest_salary,
    COALESCE(b.bonus_sum, 0) AS `sum(bonus)`
FROM employee emp
LEFT JOIN (
    -- 获取每个员工指定日期范围内的最新薪资
    SELECT
        employee_id,
        amount AS newest_salary
    FROM (
        SELECT
            employee_id,
            amount,
            ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY date_paid DESC) AS rn
        FROM payment_record
        WHERE type = 1 
          AND date_paid BETWEEN '2022-10-01' AND '2022-11-01'
    ) t
    WHERE rn = 1
) s ON emp.id = s.employee_id
LEFT JOIN (
    -- 计算每个员工指定日期范围内的奖金总额
    SELECT
        employee_id,
        SUM(amount) AS bonus_sum
    FROM payment_record
    WHERE type IN (2,3,4)
      AND date_paid BETWEEN '2022-10-01' AND '2022-11-01'
    GROUP BY employee_id
) b ON emp.id = b.employee_id
ORDER BY emp.id;

逻辑说明 #

1. 最新薪资获取：通过ROW_NUMBER()窗口函数，按员工分组、支付日期降序排序，取排名为1的记录，即为该员工指定日期内的最新薪资
2. 奖金总额计算：直接筛选type为2-4的记录，按员工分组求和
3. 关联员工表：用LEFT JOIN确保即使员工无薪资/奖金记录也能被查询到，COALESCE()处理NULL值为0

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