使用LAG函数关联表时无法捕获上一条记录信息的问题求助

嘿，我来帮你搞定这个问题！先理清楚你的核心需求：你需要关联Employee和EmployeeSalary两张表，拿到薪资信息的同时，从Employee表中获取12-Jan-23至29-Jan-2023期间生效的手机号，以及当前有效的部门名称（也就是EndDate为31-Dec-9999的那条记录里的DeptName）。你尝试用LAG函数获取历史手机号，但关联后没得到预期结果，对吧？

问题分析 #

你之前的LAG函数没生效，大概率是因为没有正确设置分区和排序规则，或者关联时没过滤到目标记录。毕竟你要的历史手机号和当前部门名称，其实是Employee表中同一员工的两条不同记录，得分别定位到这两条数据再和薪资表关联。

解决方案 #

这里给你两种实现思路，都能得到你想要的输出：

方案1：直接筛选目标记录关联（更直观）

这种方式不需要用LAG，直接从Employee表中筛选出你需要的两条记录，再和薪资表关联：

SELECT 
    es.Employee,
    es.Salary,
    es.StartDate,
    es.EndDate,
    curr_dept.Dept,
    hist_phone.PhoneNum AS PhoneNumber,
    curr_dept.DeptName AS DepartmentName
FROM EmployeeSalary es
-- 关联获取指定时间段的历史手机号
JOIN (
    SELECT Employee, PhoneNum
    FROM Employee
    WHERE StartDate = '12-Jan-23' AND EndDate = '29-Jan-2023'
) hist_phone ON es.Employee = hist_phone.Employee
-- 关联获取当前有效的部门信息
JOIN (
    SELECT Employee, Dept, DeptName
    FROM Employee
    WHERE EndDate = '31-Dec-9999'
) curr_dept ON es.Employee = curr_dept.Employee
-- 可选：如果只需要特定员工的结果，加上这个过滤
WHERE es.Employee = 50;

方案2：用LAG函数获取历史手机号（贴合你的尝试）

如果你坚持要用LAG，可以先在Employee表中对每个员工按入职时间排序，再用LAG取上一条记录的手机号，最后关联薪资表：

WITH EmployeeWithHistory AS (
    SELECT 
        Employee,
        PhoneNum,
        Dept,
        DeptName,
        StartDate,
        EndDate,
        -- 按员工分区，按生效时间升序，取上一条的手机号
        LAG(PhoneNum) OVER (PARTITION BY Employee ORDER BY StartDate) AS PreviousPhoneNum
    FROM Employee
)
SELECT 
    es.Employee,
    es.Salary,
    es.StartDate,
    es.EndDate,
    e.Dept,
    e.PreviousPhoneNum AS PhoneNumber,
    e.DeptName AS DepartmentName
FROM EmployeeSalary es
-- 关联到当前有效的部门记录，此时PreviousPhoneNum就是历史手机号
JOIN EmployeeWithHistory e ON es.Employee = e.Employee
WHERE e.EndDate = '31-Dec-9999'
AND es.Employee = 50;

为什么之前的LAG没生效？ #

大概率是这两个原因：

• 没有用PARTITION BY Employee分区，导致LAG函数跨员工取了记录；
• 排序字段不是StartDate，或者排序方向不对，导致取到的不是你要的那条历史手机号；
• 关联时没有过滤EndDate = '31-Dec-9999'的记录，导致拿到的不是当前部门对应的历史手机号。

这两种方案都能输出你想要的结果，你可以根据自己的习惯选择~

备注：内容来源于stack exchange，提问作者VB IN