如何使用SQL查询两个日期间的第1、3个周六及全部周日

给定一个日期范围，需要返回该范围内符合以下条件的所有周六和周日：

• 仅保留周六对应所属月份的第1个或第3个周六（排序规则以自然月为维度，不是整个日期范围的全局排序）
• 保留范围内所有周日，同时标注该周日在其所属月份的排序位次

示例 #

当起始日期为2021-08-15、结束日期为2021-09-20时，预期输出如下：

Dates         Saturday Number (in its own month)
----------    ---------------
2021-08-21    3
2021-09-04    1
2021-09-18    3

Dates         Sunday Number (in its own month)
----------    ---------------
2021-08-15    3
2021-08-22    4
2021-08-29    5
2021-09-05    1
2021-09-12    2
2021-09-19    3

最终有效天数计算规则：总日期范围天数 - 符合条件的周六数量 - 周日数量，以上述示例计算为 37 - 3 - 6 = 28天。

原有代码的核心错误是：SatNo 字段是按递归序列全局累加计数，跨月时没有重置，所以得到的不是周六在所属自然月的排序，筛选SatNo IN (1,3)自然不符合预期。

DECLARE @sd DATETIME = '2021-08-15'
DECLARE @ed DATETIME = '2021-09-20'
SET DATEFIRST 7; -- 统一规则：周日为一周第一天，周六为一周第七天

-- 生成日期范围内所有日期
;WITH AllDates AS (
    SELECT @sd AS MyDate
    UNION ALL
    SELECT DATEADD(dd, 1, MyDate)
    FROM AllDates
    WHERE MyDate < @ed
),
-- 计算每个日期的基础属性
DateWithAttr AS (
    SELECT 
        MyDate,
        DATEPART(dw, MyDate) AS WeekDayNum,
        DATEFROMPARTS(YEAR(MyDate), MONTH(MyDate), 1) AS MonthFirstDay
    FROM AllDates
),
-- 筛选周六并计算当月排序
ValidSaturdays AS (
    SELECT
        MyDate,
        (DATEDIFF(dd, MonthFirstDay, MyDate) / 7) + 1 AS SatOrderInMonth
    FROM DateWithAttr
    WHERE WeekDayNum = 7 -- 周六
    AND (DATEDIFF(dd, MonthFirstDay, MyDate) / 7) + 1 IN (1,3)
),
-- 筛选周日并计算当月排序
AllSundays AS (
    SELECT
        MyDate,
        (DATEDIFF(dd, MonthFirstDay, MyDate) / 7) + 1 AS SunOrderInMonth
    FROM DateWithAttr
    WHERE WeekDayNum = 1 -- 周日
)

-- 输出符合条件的周六
SELECT 
    CONVERT(VARCHAR(10), MyDate, 23) AS Dates,
    SatOrderInMonth AS [Saturday Number (in its own month)]
FROM ValidSaturdays
ORDER BY MyDate;

-- 输出所有周日
SELECT 
    CONVERT(VARCHAR(10), MyDate, 23) AS Dates,
    SunOrderInMonth AS [Sunday Number (in its own month)]
FROM AllSundays
ORDER BY MyDate;

-- 输出有效天数
SELECT 
    DATEDIFF(dd, @sd, @ed) + 1 
    - (SELECT COUNT(*) FROM ValidSaturdays)
    - (SELECT COUNT(*) FROM AllSundays) AS ValidDays
OPTION(MAXRECURSION 0);

• 先递归生成日期范围内的所有日期，避免原逻辑仅从第一个周六开始统计遗漏边界日期的问题
• 当月周次排序使用「当前日期和当月第一天的天数差除以7取整+1」计算，完全匹配示例的排序规则
• 开头的SET DATEFIRST 7用于统一周计算规则，避免不同SQL环境的默认配置差异导致结果异常

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