如何在MSSQL中实现发票表与假期表的日期匹配查询？

场景说明 #

现有两张表：

• Invoices（发票表）：包含发票ID、员工ID、发票日期
• Holidays（假期表）：包含员工ID、假期开始/结束日期（部分员工存在多条假期记录）

需求：对每一条发票记录，判断其日期是否属于对应员工的任意一段假期内，是则calc_flag=0，否则calc_flag=1。

方案1：使用EXISTS子查询（推荐，性能更优） #

这种方式无需处理JOIN后的重复行，只要找到任意一条匹配的假期记录就判定为假期，逻辑简洁且高效：

SELECT
    InvoiceID,
    EmployeeID,
    InvoiceDate,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM Holidays h
            WHERE h.EmployeeID = i.EmployeeID
                AND i.InvoiceDate BETWEEN h.StartDate AND h.EndDate
        ) THEN 0
        ELSE 1
    END AS calc_flag
FROM
    Invoices i

方案2：使用LEFT JOIN + GROUP BY #

通过左连接关联两张表，再通过分组去重（避免同一发票匹配多条假期记录导致重复行）：

SELECT
    i.InvoiceID,
    i.EmployeeID,
    i.InvoiceDate,
    CASE
        WHEN h.EmployeeID IS NOT NULL THEN 0
        ELSE 1
    END AS calc_flag
FROM
    Invoices i
LEFT JOIN
    Holidays h 
    ON i.EmployeeID = h.EmployeeID
    AND i.InvoiceDate BETWEEN h.StartDate AND h.EndDate
GROUP BY
    i.InvoiceID, i.EmployeeID, i.InvoiceDate, h.EmployeeID

关键逻辑说明 #

• 两种方案都通过i.InvoiceDate BETWEEN h.StartDate AND h.EndDate判断发票日期是否落在假期区间内
• 方案1的EXISTS子查询会在找到第一条匹配的假期记录后立即停止，适合假期表数据量较大的场景
• 方案2的LEFT JOIN会先返回所有匹配的假期记录，再通过GROUP BY合并重复的发票记录，确保每条发票只返回一行结果

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