如何使用SQL按起止日期范围将单条记录拆分为多行

以下实现默认你的原始表名为t_range，可根据实际业务的表名替换。

核心逻辑为：生成覆盖你所有数据时间范围的连续日期序列，将原表与日期序列关联，关联条件为连续日期落在原表的start_date和end_date区间内。

PostgreSQL 版本 #

可直接调用内置的generate_series函数生成连续日期：

SELECT 
    t.id,
    t.start_date,
    t.end_date,
    t.amount,
    s.operation_date
FROM t_range t
CROSS JOIN generate_series(t.start_date, t.end_date, '1 day'::interval) AS s(operation_date);

MySQL 8.0+ 版本 #

用递归CTE生成连续日期序列后关联：

WITH RECURSIVE date_series AS (
    -- 取最小起始日期作为序列起点
    SELECT MIN(start_date) AS dt FROM t_range
    UNION ALL
    SELECT dt + INTERVAL 1 DAY FROM date_series 
    WHERE dt < (SELECT MAX(end_date) FROM t_range)
)
SELECT 
    t.id,
    t.start_date,
    t.end_date,
    t.amount,
    d.dt AS operation_date
FROM t_range t
JOIN date_series d ON d.dt BETWEEN t.start_date AND t.end_date;

如果是低版本MySQL不支持CTE，可提前维护一张日期维度表，直接用维度表和原表按上述关联条件关联即可。

Hive/Spark SQL 版本 #

用侧视图 + 序列生成函数实现：

SELECT 
    t.id,
    t.start_date,
    t.end_date,
    t.amount,
    date_add(t.start_date, pos) AS operation_date
FROM t_range t
LATERAL VIEW posexplode(split(space(datediff(end_date, start_date)), ' ')) pe AS pos, val;

-- 高版本Spark SQL也可以直接用sequence函数简化写法
-- SELECT 
--     t.*,
--     single_date AS operation_date 
-- FROM t_range t 
-- LATERAL VIEW explode(sequence(to_date(start_date), to_date(end_date), interval 1 day)) tmp AS single_date

Oracle 版本 #

用CONNECT BY递归实现：

SELECT 
    t.id,
    t.start_date,
    t.end_date,
    t.amount,
    t.start_date + (LEVEL - 1) AS operation_date
FROM t_range t
CONNECT BY LEVEL <= (t.end_date - t.start_date + 1)
AND PRIOR t.id = t.id
AND PRIOR SYS_GUID() IS NOT NULL;

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