基于指定日期查询员工当前职位的SQL实现问题

表结构 #

Employees表（员工基础信息） #

JobCode表（员工职位及任职起始时间） #

需求 #

传入参数@ActDate，返回每位员工对应日期的职位：

• 若@ActDate在员工职位记录时间范围内，返回该日期前最新的职位
• 若@ActDate早于该员工的第一条职位记录时间，返回其首个职位

原代码问题 #

原SQL代码仅能处理@ActDate在员工职位记录范围内的场景，当@ActDate早于员工第一条Updated记录时，子查询无匹配结果，无法返回员工的首个职位：

SELECT e.EmpID, a.JobCode 
FROM Employee e
JOIN (
    SELECT
        EmpID,
        COALESCE(JobCode,'BEFORE'),
        ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Updated DESC) AS mdr
    FROM [JobCode]
    WHERE Updated <= @ActDate
) a ON a.EmpID = e.EmpID
WHERE a.mdr = 1

解决方案 #

方案一：子查询结合COALESCE #

通过两次子查询分别获取符合条件的最新职位和员工的首个职位，用COALESCE优先取前者，前者不存在时取后者：

SELECT 
    e.EmpID,
    COALESCE(
        -- 获取@ActDate之前最新的职位
        (SELECT TOP 1 JobCode 
         FROM JobCode j 
         WHERE j.EmpID = e.EmpID AND j.Updated <= @ActDate 
         ORDER BY j.Updated DESC),
        -- 无符合条件记录时获取首个职位
        (SELECT TOP 1 JobCode 
         FROM JobCode j 
         WHERE j.EmpID = e.EmpID 
         ORDER BY j.Updated ASC)
    ) AS JobCode
FROM Employees e

方案二：窗口函数统一处理 #

通过窗口函数对每个员工的职位记录排序，优先筛选@ActDate之前的最新记录，无匹配时自动取最早的职位：

WITH JobCTE AS (
    SELECT 
        j.EmpID,
        j.JobCode,
        j.Updated,
        -- 排序规则：先取<=@ActDate的记录并按时间倒排；无匹配时取所有记录按时间正排
        ROW_NUMBER() OVER (
            PARTITION BY j.EmpID 
            ORDER BY 
                CASE WHEN j.Updated <= @ActDate THEN 0 ELSE 1 END,
                CASE WHEN j.Updated <= @ActDate THEN j.Updated ELSE NULL END DESC,
                j.Updated ASC
        ) AS rn
    FROM JobCode j
)
SELECT e.EmpID, cte.JobCode
FROM Employees e
JOIN JobCTE cte ON e.EmpID = cte.EmpID
WHERE cte.rn = 1

预期结果验证 #

当@ActDate = '2023-03-20'时 #

当@ActDate = '2022-12-10'时 #

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