oracle？mysql？SqlServer？

通用：做一张日历表，然后jion日历表查询

如果有详细日期字段，可以用日期函数来判断周几，注意 默认周日是1

直接帆软公式即可求出，将公式中的日期，换成参数即可

count(GREPARRAY(RANGE(TODATE('2024-08-25'),TODATE('2024-09-10')), WEEKDAY(item) = 0 || WEEKDAY(item) = 6))

----------

DECLARE @StartDate DATE = '2024-01-01';

DECLARE @EndDate DATE = '2024-12-31';

SELECT COUNT(*) AS weekend_days

FROM (

    SELECT DATEADD(DAY, number, @StartDate) AS date

    FROM master..spt_values

    WHERE type = 'P' 

    AND number 72024010120241231<= DATEDIFF(DAY, @StartDate, @EndDate)

) dates

WHERE DATEPART(WEEKDAY, date) IN (1, );

---------

SELECT COUNT(*) AS weekend_days

FROM (

    SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS date

    FROM (

        SELECT a.N + b.N*10 + c.N*100 AS n

        FROM 

            (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,

            (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,

            (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c

        WHERE a.N + b.N*10 + c.N*100 2022022022020101202202401017<= DATEDIFF('4-12-31', '4-01-01')

    ) numbers

    WHERE DATE_ADD('4-01-01', INTERVAL n DAY) BETWEEN '4--' AND '4-12-31'

    AND DAYOFWEEK(DATE_ADD('--', INTERVAL n DAY)) IN (1, )

) weekends;

WITH RECURSIVE date_range AS (

SELECT '2023-01-01' AS dt  -- 开始日期

UNION ALL

SELECT DATE_ADD(dt, INTERVAL 1 DAY)

FROM date_range

WHERE dt < '2023-01-31'  -- 结束日期

)

SELECT

SUM(CASE WHEN DAYOFWEEK(dt) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_days

FROM date_range;

试试这个，我这个是oracle的用法

通用的做法就是1楼说的做一个日期维度表，不管你用什么数据库都可以用了，哪里还用在乎什么语法不同，而且去写一堆复杂的查询？

日期维度表基本上可以搞定99%相关日期的事情，如果还有1%搞不定，那么可以身自定义一张表，或者增加列就行了，反正自己上传一张到2050年的日期维度 表基本上开发都能省事了！！

有了日期维度表，常用的关系性数据库随便你整 ，写一个关联查询一下就出来了。都不用考虑什么星期是0的那个美国方式了，如果要特殊的各种计算日期维度你就增加列就行了！