如何用SQL为表中日期列的每个日期生成日期范围

数据老陈

2026-6-19

基于日期列生成前后N天范围的SQL实现

需求说明

现有表table1包含id和日期列mydate，数据示例如下：

id    mydate
  1     06/07/2024
  8     05/03/2024

需要为每条记录生成包含mydate前后2天的日期范围，最终生成新表的预期结果如下：

id   yourdate
   1    06/09/2024
   1    06/08/2024
   1    06/07/2024
   1    06/06/2024
   1    06/05/2024
   8    05/05/2024
   8    05/04/2024
   8    05/03/2024
   8    05/02/2024
   8    05/01/2024

解决方案

核心思路是通过日期偏移+序列生成，为每个原始日期扩展出连续的日期范围。以下是主流数据库的具体实现：

1. MySQL/MariaDB（8.0+）

利用递归CTE生成日期序列：

WITH RECURSIVE date_ranges AS (
    SELECT id, mydate, 2 AS days_offset
    FROM table1
    UNION ALL
    SELECT id, DATE_SUB(mydate, INTERVAL 1 DAY), days_offset - 1
    FROM date_ranges
    WHERE days_offset > -2
)
SELECT id, DATE_FORMAT(mydate, '%m/%d/%Y') AS yourdate
FROM date_ranges
ORDER BY id, yourdate DESC;

2. PostgreSQL

使用generate_series函数直接生成偏移序列：

SELECT 
    t.id,
    TO_CHAR(t.mydate + s.days * INTERVAL '1 day', 'MM/DD/YYYY') AS yourdate
FROM table1 t
CROSS JOIN generate_series(-2, 2) s(days)
ORDER BY t.id, yourdate DESC;

3. SQL Server

结合数字表与日期偏移函数：

WITH nums AS (
    SELECT -2 AS n UNION ALL SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
)
SELECT 
    t.id,
    FORMAT(DATEADD(day, n.n, t.mydate), 'MM/dd/yyyy') AS yourdate
FROM table1 t
CROSS JOIN nums n
ORDER BY t.id, yourdate DESC;

4. Oracle

通过CONNECT BY生成连续序列：

SELECT 
    t.id,
    TO_CHAR(t.mydate + (level - 3), 'MM/DD/YYYY') AS yourdate
FROM table1 t
CONNECT BY LEVEL <= 5
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY t.id, yourdate DESC;