Oracle SQL：如何将状态起止日期范围拆分为每日记录（无需日历表）

我有一张记录设备状态（IN_SVC/OUT_SVC）及对应状态起止日期的表，表结构及示例数据如下：

需要将其转换为每个状态对应单独日期的记录，无需创建日历表，期望输出如下：

解决方案 #

1. MySQL 8.0+（递归CTE实现） #

利用递归公共表表达式生成日期序列，无需额外日历表：

WITH RECURSIVE date_range AS (
    SELECT 
        EQUIP_ID,
        STATUS,
        STR_TO_DATE(STATUS_START, '%m/%d/%Y') AS status_date,
        STR_TO_DATE(STATUS_END, '%m/%d/%Y') AS end_date
    FROM equipment_status
    UNION ALL
    SELECT 
        EQUIP_ID,
        STATUS,
        DATE_ADD(status_date, INTERVAL 1 DAY),
        end_date
    FROM date_range
    WHERE status_date < end_date - INTERVAL 1 DAY -- 避免状态交接日期重复
)
SELECT 
    EQUIP_ID,
    STATUS,
    DATE_FORMAT(status_date, '%m/%d/%Y') AS STATUS_DATE
FROM date_range
ORDER BY EQUIP_ID, status_date;

2. PostgreSQL（generate_series函数） #

借助内置generate_series函数直接生成日期范围：

SELECT 
    es.EQUIP_ID,
    es.STATUS,
    TO_CHAR(d.status_date, 'MM/DD/YYYY') AS STATUS_DATE
FROM equipment_status es
CROSS JOIN LATERAL generate_series(
    TO_DATE(es.STATUS_START, 'MM/DD/YYYY'),
    TO_DATE(es.STATUS_END, 'MM/DD/YYYY') - INTERVAL '1 day',
    INTERVAL '1 day'
) AS d(status_date)
-- 补充最后一个状态的结束日期
UNION ALL
SELECT 
    EQUIP_ID,
    STATUS,
    STATUS_END AS STATUS_DATE
FROM equipment_status es
WHERE NOT EXISTS (
    SELECT 1 FROM equipment_status es2 
    WHERE es2.EQUIP_ID = es.EQUIP_ID 
    AND es2.STATUS_START = es.STATUS_END
)
ORDER BY EQUIP_ID, STATUS_DATE;

3. SQL Server（递归CTE实现） #

WITH date_range AS (
    SELECT 
        EQUIP_ID,
        STATUS,
        CONVERT(DATE, STATUS_START, 101) AS status_date,
        CONVERT(DATE, STATUS_END, 101) AS end_date
    FROM equipment_status
    UNION ALL
    SELECT 
        EQUIP_ID,
        STATUS,
        DATEADD(DAY, 1, status_date),
        end_date
    FROM date_range
    WHERE status_date < DATEADD(DAY, -1, end_date) -- 跳过交接日期
)
SELECT 
    EQUIP_ID,
    STATUS,
    FORMAT(status_date, 'MM/dd/yyyy') AS STATUS_DATE
FROM date_range
-- 补充无后续状态的结束日期
UNION ALL
SELECT 
    EQUIP_ID,
    STATUS,
    FORMAT(CONVERT(DATE, STATUS_END, 101), 'MM/dd/yyyy') AS STATUS_DATE
FROM equipment_status es
WHERE NOT EXISTS (
    SELECT 1 FROM equipment_status es2 
    WHERE es2.EQUIP_ID = es.EQUIP_ID 
    AND es2.STATUS_START = es.STATUS_END
)
ORDER BY EQUIP_ID, status_date
OPTION (MAXRECURSION 0); -- 取消递归次数限制，适配大日期跨度

说明 #

• 所有方案均无需创建额外日历表，通过递归或内置函数生成日期序列
• 针对状态交接日期（如示例中的07/21/2020），通过调整日期范围确保每个日期仅归属一个状态
• 若数据库日期格式与示例不同，可修改日期转换函数的格式参数（如%m/%d/%Y、101等）

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