SQL查询匹配员工请假与入场记录 现有查询结果不符合预期如何修正

问题原因 #

你的SQL存在三个核心问题导致输出不符合预期：

• 错误的日期过滤逻辑：WHERE子句中EnterTable.EnterDate>LeaveTable.LeaveDate要求入场时间晚于请假时间，但你期望结果中yaser的入场时间1400/01/07早于请假时间1400/05/02，直接被该条件过滤，无法出现在结果中。
• JOIN逻辑冗余：你使用了LEFT JOIN后又对右表EnterTable的字段做非空过滤，实际上等价于INNER JOIN，既不符合LEFT JOIN的使用场景，也会产生不必要的性能消耗。
• 多对多关联产生冗余数据：同一名员工的所有请假记录会和所有入场记录做笛卡尔积关联，比如yaser有3条请假记录、1条入场记录，关联后会产生3条重复的员工数据，和示例输出的单条结果不符。

修正方案 #

方案1：返回所有符合员工匹配的记录 #

如果你的需求是只要员工存在入场记录，就返回该员工所有对应的请假+入场关联记录，直接移除错误的日期过滤条件，替换为更合理的INNER JOIN即可：

SELECT
    LeaveTable.EmpName,
    LeaveTable.EmpFamily,
    LeaveTable.EmpFatherName,
    LeaveTable.LeaveDate,
    EnterTable.EnterDate 
FROM LeaveTable
INNER JOIN EnterTable
    ON LeaveTable.EmpName = EnterTable.EmpName 
    AND LeaveTable.EmpFamily = EnterTable.EmpFamily 
    AND LeaveTable.EmpFatherName = EnterTable.EmpFatherName
ORDER BY 
    EmpName, EmpFamily, EmpFatherName, EnterTable.EnterDate, LeaveTable.LeaveDate

执行后会返回majid的2条关联记录、hosssein的1条关联记录、yaser的3条关联记录，共6条匹配结果。

方案2：返回和示例完全一致的结果 #

如果需要每个员工仅返回1条记录，和你给出的期望输出完全匹配，可以用窗口函数做优先级排序后取第一条即可：

WITH leave_with_enter AS (
    SELECT
        LeaveTable.EmpName,
        LeaveTable.EmpFamily,
        LeaveTable.EmpFatherName,
        LeaveTable.LeaveDate,
        EnterTable.EnterDate,
        ROW_NUMBER() OVER (
            PARTITION BY LeaveTable.EmpName, LeaveTable.EmpFamily, LeaveTable.EmpFatherName 
            ORDER BY 
                -- 优先取入场时间晚于请假时间的记录
                CASE WHEN EnterTable.EnterDate > LeaveTable.LeaveDate THEN 1 ELSE 2 END,
                -- 同优先级下取和请假日期差最小的记录
                ABS(DATEDIFF(EnterTable.EnterDate, LeaveTable.LeaveDate)) ASC
        ) AS rn
    FROM LeaveTable
    INNER JOIN EnterTable
        ON LeaveTable.EmpName = EnterTable.EmpName 
        AND LeaveTable.EmpFamily = EnterTable.EmpFamily 
        AND LeaveTable.EmpFatherName = EnterTable.EmpFatherName
)
SELECT 
    EmpName,
    EmpFamily,
    EmpFatherName,
    LeaveDate,
    EnterDate
FROM leave_with_enter
WHERE rn = 1
ORDER BY EmpName

注：DATEDIFF函数根据你使用的数据库类型可能存在语法差异，可调整为对应数据库的日期差值计算函数即可。

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