SQL带条件的工作日计数器实现需求求助

假设你的日历表名为Calendar，核心字段为CalendarDate DATE、IsWeekend INT、IsHoliday INT，以下是针对两个需求的具体实现：

需求1：统计每个月的工作日数量 #

工作日判定逻辑为既非周末也非节假日（即IsWeekend = 0 AND IsHoliday = 0）。按年份和月份分组，统计符合条件的日期数量即可：

SELECT
    DATEPART(YEAR, CalendarDate) AS [Year],
    DATEPART(MONTH, CalendarDate) AS [Month],
    DATENAME(MONTH, CalendarDate) AS [MonthName],
    COUNT(CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 END) AS WorkDayCount
FROM Calendar
GROUP BY DATEPART(YEAR, CalendarDate), DATEPART(MONTH, CalendarDate), DATENAME(MONTH, CalendarDate)
ORDER BY [Year], [Month];

说明 #

• 用DATEPART提取年、月用于分组和排序，DATENAME返回月份名称（可选）
• COUNT(CASE ...)仅统计满足工作日条件的记录，逻辑直观清晰

需求2：生成继承式Counter字段 #

Counter规则：工作日时递增，非工作日（周末/节假日/两者皆是）继承前一日的Counter值。无需变量或递归，用窗口函数即可实现：

WITH WorkDayCTE AS (
    SELECT
        CalendarDate,
        IsWeekend,
        IsHoliday,
        -- 标记当前日期是否为工作日，是则记1，否则记0
        CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 ELSE 0 END AS IsWorkDay,
        -- 计算截止到当前日期的累计工作日数（作为基准值）
        SUM(CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 ELSE 0 END) OVER(ORDER BY CalendarDate) AS BaseCounter
    FROM Calendar
)
SELECT
    CalendarDate,
    IsWeekend,
    IsHoliday,
    -- 非工作日取上一个工作日的BaseCounter；工作日直接用当前BaseCounter
    LAST_VALUE(CASE WHEN IsWorkDay = 1 THEN BaseCounter END) OVER(
        ORDER BY CalendarDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Counter
FROM WorkDayCTE
ORDER BY CalendarDate;

说明 #

1. CTE部分：先标记每个日期是否为工作日，同时计算累计工作日总数BaseCounter（仅在工作日时递增）
2. 主查询部分：用LAST_VALUE窗口函数，在从起始到当前行的范围内，取最近一个工作日的BaseCounter，自动实现非工作日继承前一日Counter的逻辑

如果是MySQL环境，可将LAST_VALUE替换为MAX(CASE ...)，逻辑完全一致：

-- MySQL兼容版
WITH WorkDayCTE AS (
    SELECT
        CalendarDate,
        IsWeekend,
        IsHoliday,
        CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 ELSE 0 END AS IsWorkDay,
        SUM(CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 ELSE 0 END) OVER(ORDER BY CalendarDate) AS BaseCounter
    FROM Calendar
)
SELECT
    CalendarDate,
    IsWeekend,
    IsHoliday,
    MAX(CASE WHEN IsWorkDay = 1 THEN BaseCounter END) OVER(
        ORDER BY CalendarDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Counter
FROM WorkDayCTE
ORDER BY CalendarDate;

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