如何用SQL生成含连续周日及对应后续7个周日的表格？

当然可以实现这个需求，核心思路是先生成连续的周日序列，再为每个周日计算并拼接出对应的后续7个周日。下面针对不同主流SQL数据库给出具体实现方案：

MySQL 8.0+ 版本 #

WITH recursive sundays AS (
    -- 设定起始周日，这里用2023年第一个周日举例
    SELECT DATE('2023-01-01') AS sunday_date
    UNION ALL
    -- 递归生成后续每一周的周日
    SELECT DATE_ADD(sunday_date, INTERVAL 7 DAY)
    FROM sundays
    -- 设定结束日期，按需调整
    WHERE sunday_date <= DATE('2023-12-31')
),
sunday_sequences AS (
    SELECT 
        s.sunday_date,
        -- 拼接出当前周日开始的7个连续周日
        GROUP_CONCAT(
            DATE_ADD(s.sunday_date, INTERVAL (n-1)*7 DAY)
            ORDER BY n SEPARATOR ', '
        ) AS next_seven_sundays
    FROM sundays s
    -- 生成1到7的数字，用来计算后续周日
    CROSS JOIN (SELECT 1 AS 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) nums
    GROUP BY s.sunday_date
)
SELECT * FROM sunday_sequences;

SQL Server 版本 #

WITH sundays AS (
    -- 起始周日
    SELECT CAST('2023-01-01' AS DATE) AS sunday_date
    UNION ALL
    -- 递归生成后续周日
    SELECT DATEADD(DAY, 7, sunday_date)
    FROM sundays
    WHERE sunday_date <= CAST('2023-12-31' AS DATE)
),
sunday_sequences AS (
    SELECT 
        s.sunday_date,
        -- 拼接7个连续周日
        STRING_AGG(
            DATEADD(DAY, (n-1)*7, s.sunday_date), ', '
        ) WITHIN GROUP (ORDER BY n) AS next_seven_sundays
    FROM sundays s
    -- 生成1-7的数字序列
    CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7)) nums(n)
    GROUP BY s.sunday_date
)
SELECT * FROM sunday_sequences
OPTION (MAXRECURSION 0); -- 解除默认递归次数限制

PostgreSQL 版本 #

WITH sundays AS (
    -- 直接生成指定范围内的所有周日
    SELECT generate_series(
        DATE '2023-01-01',
        DATE '2023-12-31',
        INTERVAL '7 days'
    )::DATE AS sunday_date
),
sunday_sequences AS (
    SELECT 
        s.sunday_date,
        -- 拼接7个连续周日
        STRING_AGG(
            (s.sunday_date + (n-1)*INTERVAL '7 days')::DATE, ', '
            ORDER BY n
        ) AS next_seven_sundays
    FROM sundays s
    CROSS JOIN generate_series(1,7) AS nums(n)
    GROUP BY s.sunday_date
)
SELECT * FROM sunday_sequences;

调整说明 #

• 可以修改起始日期、结束日期来适配你的时间范围
• 如果第二列不需要包含当前周日，只取后续7个，把代码里的(n-1)改成n即可

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