SQL实现：根据日期计算月份内指定工作日的出现次数

核心逻辑是：锁定目标月份的日期范围，筛选出与输入日期星期几相同的日期，再排除非工作日后统计数量。以下是主流数据库的具体实现：

MySQL/MariaDB #

假设输入日期为'2024-05-15'（可替换为变量或表字段）：

SELECT COUNT(*) AS expectation
FROM (
    SELECT DATE_ADD(
        DATE_FORMAT('2024-05-15', '%Y-%m-01'),
        INTERVAL (n - 1) DAY
    ) AS date_of_month
    FROM (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
        SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
        SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
        SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
        SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
        SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
        SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31
    ) AS nums
    WHERE DATE_ADD(
        DATE_FORMAT('2024-05-15', '%Y-%m-01'),
        INTERVAL (n - 1) DAY
    ) <= LAST_DAY('2024-05-15')
) AS month_dates
WHERE 
    WEEKDAY(date_of_month) = WEEKDAY('2024-05-15') -- 匹配相同星期几（WEEKDAY：周一=0，周日=6）
    AND WEEKDAY(date_of_month) < 5; -- 仅保留周一到周五

PostgreSQL #

输入日期为'2024-05-15'：

SELECT COUNT(*) AS expectation
FROM generate_series(
    DATE_TRUNC('month', '2024-05-15'::DATE),
    DATE_TRUNC('month', '2024-05-15'::DATE) + INTERVAL '1 month - 1 day',
    INTERVAL '1 day'
) AS month_dates(date_val)
WHERE
    EXTRACT(DOW FROM date_val) = EXTRACT(DOW FROM '2024-05-15'::DATE) -- 匹配星期几（DOW：周日=0，周六=6）
    AND EXTRACT(DOW FROM date_val) BETWEEN 1 AND 5; -- 仅保留周一到周五

SQL Server #

输入日期为'2024-05-15'：

SELECT COUNT(*) AS expectation
FROM (
    SELECT DATEADD(DAY, n-1, DATEFROMPARTS(YEAR('2024-05-15'), MONTH('2024-05-15'), 1)) AS date_of_month
    FROM (
        SELECT TOP 31 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_columns
    ) AS nums
    WHERE DATEADD(DAY, n-1, DATEFROMPARTS(YEAR('2024-05-15'), MONTH('2024-05-15'), 1)) <= EOMONTH('2024-05-15')
) AS month_dates
WHERE
    DATEPART(WEEKDAY, date_of_month) = DATEPART(WEEKDAY, '2024-05-15') -- 匹配相同星期几
    AND DATEPART(WEEKDAY, date_of_month) BETWEEN 2 AND 6; -- 默认周日=1，周一=2，周五=6；若需周一为起始日，可先执行SET DATEFIRST 1，此时工作日对应1-5

参数化批量处理（以MySQL为例） #

如果要从表中读取目标日期批量计算，假设表名为date_records，日期字段为target_date：

SELECT 
    target_date,
    (
        SELECT COUNT(*)
        FROM (
            SELECT DATE_ADD(DATE_FORMAT(d.target_date, '%Y-%m-01'), INTERVAL (n-1) DAY) AS dt
            FROM (SELECT 1 n UNION ALL SELECT 2 ... UNION ALL SELECT 31) nums
            WHERE DATE_ADD(DATE_FORMAT(d.target_date, '%Y-%m-01'), INTERVAL (n-1) DAY) <= LAST_DAY(d.target_date)
        ) md
        WHERE WEEKDAY(md.dt) = WEEKDAY(d.target_date) AND WEEKDAY(md.dt) <5
    ) AS expectation
FROM date_records d;

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