如何优化SQL查询以仅获取同组的上一条PayId记录？

问题分析 #

原查询的核心问题是：仅通过b.paymentDate < a.paymentDate的条件关联，会返回同组内所有支付日期更早的历史记录，而非当前记录之前最近的那一条。要实现目标，需要先对分组内的薪资记录按支付日期排序，精准匹配每个记录的前序最近条目。

预处理：生成薪资汇总数据集 #

先关联两张表，按payId和employeeId汇总住房补贴与薪资，得到基础分析数据集：

WITH PayrollSummary AS (
    SELECT 
        p.payId,
        p.payName,
        p.groupId,
        p.paymentDate,
        pi.employeeId,
        SUM(CASE WHEN pi.payCategory = 'housing' THEN pi.value ELSE 0 END) AS housing,
        SUM(CASE WHEN pi.payCategory = 'salary' THEN pi.value ELSE 0 END) AS salary
    FROM Payroll p
    JOIN PayrollItems pi ON p.payId = pi.payId
    GROUP BY p.payId, p.payName, p.groupId, p.paymentDate, pi.employeeId
)

注：用CASE WHEN替代原查询的value * (payCategory = 'housing')，适配更多数据库语法规范。

方案1：用窗口函数实现精准匹配（推荐） #

利用LAG()窗口函数，按groupId和employeeId分组、paymentDate排序，直接获取每个记录的前序最近payId，再关联回汇总表拿到完整信息：

SELECT 
    curr.payId AS 当前薪资ID,
    curr.payName AS 当前薪资名称,
    prev.payId AS 前序薪资ID,
    prev.payName AS 前序薪资名称,
    prev.employeeId AS 员工ID,
    prev.housing AS 住房补贴,
    prev.salary AS 薪资,
    prev.paymentDate AS 前序支付日期
FROM (
    SELECT 
        *,
        LAG(payId) OVER (PARTITION BY groupId, employeeId ORDER BY paymentDate) AS 前序薪资ID
    FROM PayrollSummary
) curr
JOIN PayrollSummary prev ON curr.前序薪资ID = prev.payId
ORDER BY curr.payId, prev.payId, prev.employeeId;

关键逻辑说明： #

• PARTITION BY groupId, employeeId：限定仅在同组同员工范围内查找前序记录（若无需按员工过滤，可去掉employeeId）
• LAG(payId) OVER (...)：按支付日期升序，获取当前记录的上一条最近记录的payId

方案2：无窗口函数的兼容写法 #

如果数据库不支持窗口函数，可通过关联子查询找到每个记录的最大前序支付日期，再匹配对应payId：

SELECT 
    curr.payId AS 当前薪资ID,
    curr.payName AS 当前薪资名称,
    prev.payId AS 前序薪资ID,
    prev.payName AS 前序薪资名称,
    prev.employeeId AS 员工ID,
    prev.housing AS 住房补贴,
    prev.salary AS 薪资,
    prev.paymentDate AS 前序支付日期
FROM PayrollSummary curr
JOIN PayrollSummary prev 
    ON prev.groupId = curr.groupId
    AND prev.employeeId = curr.employeeId
    AND prev.paymentDate = (
        SELECT MAX(paymentDate)
        FROM PayrollSummary
        WHERE groupId = curr.groupId
          AND employeeId = curr.employeeId
          AND paymentDate < curr.paymentDate
    )
ORDER BY curr.payId, prev.payId, prev.employeeId;

测试验证结果 #

基于给定测试数据，优化后的查询会精准返回：

• groupId=2的July A（payId=21）对应前序June A（payId=20）的同员工记录
• groupId=1的July B（payId=19）对应前序同员工14的April A（payId=17）记录
• 无符合条件前序记录的条目（如groupId=1的May A）会被自动排除

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