SQL递归CTE统计2021.2.24-2.28工作日天数返回计数异常

• 日期序列生成逻辑完全偏离目标统计区间
  • 递归CTE的初始锚点未替换为统计起始日期，仍保留getdate()作为序列起点，即从你执行查询的当天开始逐天累加日期
  • 递归终止条件仅设置了日期上限（2021-02-28），未设置区间下限校验。如果执行查询的时间早于2021-02-28，生成的序列就会包含区间范围外的早于2021-02-24的日期，这就是计数结果中出现非目标区间日期的直接原因
  • 存在基础语法错误：上限日期的字符串未闭合引号（写为('02-28-2021)），同时使用MM-DD-YYYY的非通用日期格式，容易因数据库语言环境不同出现日期解析偏差
• DATEPART(w, 日期)的取值并非固定常量
  • 该函数返回的周几序号完全受当前会话的DATEFIRST参数（即每周首日的设置）影响：
    • 若采用默认的DATEFIRST=7（周首为周日），序号对应为：周日=1、周一=2、周二=3、周三=4、周四=5、周五=6、周六=7
    • 若采用国内常用的DATEFIRST=1（周首为周一），序号对应为：周一=1、周二=2、周三=3、周四=4、周五=5、周六=6、周日=7
  • 你使用DatePart(w, DateValue) > 1 AND <7的模糊范围筛选，本质是排除“周首日”和“周末日”的模糊匹配，根本不是精准锁定周三、周四、周五三个固定周几，自然会出现不符合预期的计数
• 统计逻辑设计冗余
  • 当前写法按周几序号分组后，用窗口函数计算总计数，逻辑冗余且容易受分组逻辑影响出错。实际上直接在筛选后用COUNT()统计总数即可，不需要额外分组和窗口函数

-- 固定周几的计算基准，避免数据库全局DATEFIRST设置的干扰
SET DATEFIRST 7;

WITH mycte AS
(
  -- 锚点直接指定区间起始日期，替换原来的getdate()
  SELECT CAST('2021-02-24' AS DATETIME) AS DateValue
  UNION ALL
  SELECT DateValue + 1
  FROM mycte
  -- 同时设置区间上下限，保证序列完全覆盖统计范围
  WHERE DateValue <= '2021-02-28'
)
-- 精准筛选周三、周四、周五（对应DATEFIRST=7时的w值为4、5、6）
SELECT COUNT(*) AS matched_day_count
FROM mycte
WHERE DATEPART(w, DateValue) IN (4, 5, 6)
-- 规避递归深度限制的报错
OPTION (MAXRECURSION 100);

上述代码生成的日期序列覆盖2021-02-24（周三）、25（周四）、26（周五）、27（周六）、28（周日），筛选后最终得到3个匹配日期，完全符合预期。

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