Oracle SQL：根据表中日期参数生成每日数据的技术需求

嘿，我来帮你搞定这个日期展开的需求！你需要把仓库的区间数据拆成每日记录，还要处理两种end_date的情况，我给你分主流数据库写了具体的实现方案，你可以根据自己用的数据库来选：

通用递归CTE方案（适用于SQL Server、MySQL 8.0+、PostgreSQL等） #

这种方法用递归公共表表达式（CTE）生成日期序列，兼容性比较好：

-- 注意：MySQL需要在WITH后加RECURSIVE关键字
WITH RECURSIVE warehouse_adjusted AS (
    -- 先统一处理end_date：NULL时替换为start_date+3650天
    SELECT 
        warehouse,
        start_date,
        CASE 
            WHEN end_date IS NULL THEN DATE_ADD(start_date, INTERVAL 3650 DAY) -- MySQL写法
            -- 其他数据库可以改成：start_date + INTERVAL '3650 days'
            ELSE end_date
        END AS end_date,
        distance
    FROM warehouse
),
date_series AS (
    -- 初始行：每条记录的start_date
    SELECT 
        warehouse,
        start_date AS run_date,
        end_date,
        distance
    FROM warehouse_adjusted
    UNION ALL
    -- 递归生成后续日期，直到run_date达到end_date
    SELECT 
        warehouse,
        DATE_ADD(run_date, INTERVAL 1 DAY), -- MySQL写法
        -- 其他数据库改成：run_date + INTERVAL '1 day'
        end_date,
        distance
    FROM date_series
    WHERE run_date < end_date
)
-- 输出最终需要的字段
SELECT warehouse, run_date, distance
FROM date_series
ORDER BY warehouse, run_date;

代码说明： #

• warehouse_adjusted CTE：先把NULL的end_date转换成start_date+3650天，让后续递归逻辑不用再判断空值，简化代码。
• date_series递归CTE：从每条记录的起始日期开始，每天加1天，直到日期不小于end_date为止，这样会自动包含起始和结束当天的记录。

PostgreSQL专属高效方案（用generate_series函数） #

PostgreSQL自带的generate_series可以直接生成日期序列，比递归更简洁高效：

SELECT 
    w.warehouse,
    generate_series(
        w.start_date,
        -- 处理end_date的逻辑：空值则取start_date+3650天
        CASE WHEN w.end_date IS NULL THEN w.start_date + INTERVAL '3650 days' ELSE w.end_date END,
        INTERVAL '1 day'
    )::DATE AS run_date,
    w.distance
FROM warehouse w
ORDER BY w.warehouse, run_date;

Oracle专属方案（用CONNECT BY LEVEL） #

如果用Oracle，你可以用层级查询来生成日期序列：

SELECT 
    warehouse,
    start_date + (LEVEL - 1) AS run_date,
    distance
FROM warehouse
CONNECT BY 
    -- 计算需要生成的天数：非空end_date则取区间天数，空则取3651天（包含起始日）
    LEVEL <= CASE 
                WHEN end_date IS NULL THEN 3651
                ELSE end_date - start_date + 1
            END
    -- 确保同一仓库的记录层级关联，避免跨仓库循环
    AND PRIOR warehouse = warehouse
    AND PRIOR SYS_GUID() IS NOT NULL
ORDER BY warehouse, run_date;

小提示： #

之前用LEVEL没成功可能是没处理好空值的天数计算，或者没加PRIOR SYS_GUID()来避免循环关联，上面的代码已经帮你补全了这些细节。

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