MySQL 5.5：查询表中各记录日期区间内的所有周日

我有一张包含ID、Name、Startdate、Enddate字段的数据表，需要查询每条记录的Startdate与Enddate之间的所有周日日期。

原数据表结构 #

预期输出 #

解决方案 #

通用思路：递归生成日期序列 #

通过递归公共表达式（CTE）生成每个记录日期区间内的所有日期，再筛选出周日记录。以下是不同数据库的实现示例：

1. SQL Server 写法

WITH DateSeries AS (
    SELECT 
        ID,
        Name,
        CAST(Startdate AS DATE) AS CurrentDate,
        CAST(Enddate AS DATE) AS Enddate
    FROM YourTable
    UNION ALL
    SELECT 
        ID,
        Name,
        DATEADD(DAY, 1, CurrentDate),
        Enddate
    FROM DateSeries
    WHERE CurrentDate < Enddate
)
SELECT 
    Name,
    FORMAT(CurrentDate, 'dd-MM-yyyy') AS Sundays
FROM DateSeries
WHERE DATEPART(WEEKDAY, CurrentDate) = 1 -- 注意：SQL Server默认周日为1，若DATEFIRST设为7则需改为7
ORDER BY Name, CurrentDate
OPTION (MAXRECURSION 0); -- 解除递归层数限制，适配长日期区间

2. MySQL 写法

WITH RECURSIVE DateSeries AS (
    SELECT 
        ID,
        Name,
        STR_TO_DATE(Startdate, '%d-%m-%Y') AS CurrentDate,
        STR_TO_DATE(Enddate, '%d-%m-%Y') AS Enddate
    FROM YourTable
    UNION ALL
    SELECT 
        ID,
        Name,
        DATE_ADD(CurrentDate, INTERVAL 1 DAY),
        Enddate
    FROM DateSeries
    WHERE CurrentDate < Enddate
)
SELECT 
    Name,
    DATE_FORMAT(CurrentDate, '%d-%m-%Y') AS Sundays
FROM DateSeries
WHERE DAYOFWEEK(CurrentDate) = 1 -- MySQL中DAYOFWEEK返回1代表周日
ORDER BY Name, CurrentDate;

3. PostgreSQL 写法

WITH DateSeries AS (
    SELECT 
        ID,
        Name,
        TO_DATE(Startdate, 'DD-MM-YYYY') AS CurrentDate,
        TO_DATE(Enddate, 'DD-MM-YYYY') AS Enddate
    FROM YourTable
    UNION ALL
    SELECT 
        ID,
        Name,
        CurrentDate + INTERVAL '1 day',
        Enddate
    FROM DateSeries
    WHERE CurrentDate < Enddate
)
SELECT 
    Name,
    TO_CHAR(CurrentDate, 'DD-MM-YYYY') AS Sundays
FROM DateSeries
WHERE EXTRACT(DOW FROM CurrentDate) = 0 -- PostgreSQL中DOW返回0代表周日
ORDER BY Name, CurrentDate;

注意事项 #

• 若日期字段存储为字符串，需先通过转换函数转为日期类型（如示例中的STR_TO_DATE、TO_DATE）。
• 不同数据库对周日的标识规则不同，需根据实际环境调整筛选条件。
• 日期区间跨度较大时，需解除递归层数限制（如SQL Server的OPTION (MAXRECURSION 0)）。

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