如何编写SQL将日期间隔拆分为每日行并新增字段整合入结果表

架构老林

2026-9-28

SQL实现日期区间拆分每日一行方案

核心逻辑是生成连续日期序列，和原表关联后过滤出落在区间内的日期即可，不同数据库实现方式如下：

PostgreSQL

直接使用内置generate_series函数生成日期序列：

SELECT 
    t.ITEM,
    t.DATE_FROM,
    t.DATE_TO,
    gs.DATE_PER_DAY
FROM 
    你的表名 t
CROSS JOIN 
    generate_series(t.DATE_FROM, t.DATE_TO, INTERVAL '1 day') AS gs(DATE_PER_DAY)
ORDER BY 
    t.ITEM, gs.DATE_PER_DAY;

MySQL 8.0+

用递归CTE生成日期序列：

WITH RECURSIVE date_series AS (
    -- 取最小起始日期作为序列起点
    SELECT MIN(DATE_FROM) AS dt FROM 你的表名
    UNION ALL
    SELECT dt + INTERVAL 1 DAY 
    FROM date_series 
    WHERE dt < (SELECT MAX(DATE_TO) FROM 你的表名)
)
SELECT 
    t.ITEM,
    t.DATE_FROM,
    t.DATE_TO,
    ds.dt AS DATE_PER_DAY
FROM 
    你的表名 t
INNER JOIN 
    date_series ds ON ds.dt BETWEEN t.DATE_FROM AND t.DATE_TO
ORDER BY 
    t.ITEM, ds.dt;

如果是低版本MySQL，可以提前构建一张存储连续数字的辅助表，通过数字偏移生成对应日期。

Oracle

使用CONNECT BY语法生成序列：

SELECT 
    t.ITEM,
    t.DATE_FROM,
    t.DATE_TO,
    t.DATE_FROM + (LEVEL - 1) AS DATE_PER_DAY
FROM 
    你的表名 t
CONNECT BY 
    LEVEL <= (t.DATE_TO - t.DATE_FROM + 1)
    AND PRIOR t.ITEM = t.ITEM
    AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY 
    t.ITEM, DATE_PER_DAY;

SQL Server

用递归CTE实现：

WITH date_series AS (
    SELECT ITEM, DATE_FROM, DATE_TO, DATE_FROM AS DATE_PER_DAY 
    FROM 你的表名
    UNION ALL
    SELECT ITEM, DATE_FROM, DATE_TO, DATEADD(DAY, 1, DATE_PER_DAY) 
    FROM date_series
    WHERE DATE_PER_DAY < DATE_TO
)
SELECT * FROM date_series
ORDER BY ITEM, DATE_PER_DAY
-- 解除递归层数限制，适配长区间场景
OPTION (MAXRECURSION 0);