SQL Server中关联工作日表与假期表，匹配对应假期值并求和

Hey there! Let's work through this problem where we need to link your working day table (Table A) with the holiday table (Table B), and calculate the total HolidayOutput for all holidays that fall between a given working day and the next working day in Table A.

表结构与现有数据 #

表A（工作日表） #

表B（假期表） #

预期输出 #

We want to get a result where each row from Table A includes the sum of HolidayOutput for holidays that lie between the current working day and the next one:

解决方案（SQL Server） #

Here's a query that uses window functions and conditional aggregation to achieve this:

WITH WorkingDaysWithNextDate AS (
    SELECT 
        PstngDate,
        WorkingDayOutput,
        -- 获取下一个工作日的日期；最后一行用远未来日期作为默认值
        LEAD(PstngDate, 1, '9999-12-31') OVER (ORDER BY PstngDate) AS NextWorkingDate
    FROM TableA
)
SELECT 
    w.PstngDate,
    w.WorkingDayOutput,
    -- 计算当前工作日到下一个工作日之间的假期输出总和
    SUM(b.HolidayOutput) AS HolidayOutput
FROM WorkingDaysWithNextDate w
LEFT JOIN TableB b 
    ON b.PstngDate > w.PstngDate 
    AND b.PstngDate < w.NextWorkingDate
    AND b.isWorkingDay = 0 -- 确保只统计假期记录
GROUP BY w.PstngDate, w.WorkingDayOutput
ORDER BY w.PstngDate;

代码解释 #

1. CTE WorkingDaysWithNextDate: 我们用LEAD()窗口函数为表A的每一行获取下一个工作日的日期。对于最后一行（没有后续工作日的行），我们设置默认值'9999-12-31'，这样可以兼容后续可能出现的假期数据，让查询更通用。
2. LEFT JOIN 与聚合: 将CTE与表B关联，筛选出那些日期严格位于当前工作日和下一个工作日之间的假期记录。SUM()函数会把匹配的HolidayOutput累加；如果间隔内没有假期，会返回NULL，和预期输出一致。
3. GROUP BY: 按工作日日期和输出值分组，确保表A的每一行在结果中只出现一次。

这个查询会生成你需要的精确结果！

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