Oracle SQL实现按周拆分日期为单日数据的查询需求

需求说明 #

现有表结构如下（假设表名为your_table）：

需要将每条记录对应的Stardate所在的连续7天拆分为单日数据，输出包含原ID、Stardate、单日日期date_days、星期名Days和原QTY的结果。

解决方案 #

使用Oracle的CONNECT BY层级查询生成连续日期，配合日期函数完成转换和格式化：

SELECT
    t.ID,
    t.Stardate,
    TO_CHAR(TO_DATE(t.Stardate, 'DD/MM/YYYY') + LEVEL - 1, 'DD/MM/YYYY') AS date_days,
    TO_CHAR(TO_DATE(t.Stardate, 'DD/MM/YYYY') + LEVEL - 1, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') AS Days,
    t.QTY
FROM
    your_table t
CONNECT BY
    LEVEL <= 7
    AND PRIOR t.ID = t.ID
    AND PRIOR SYS_GUID() IS NOT NULL -- 避免生成重复记录
ORDER BY
    t.ID,
    TO_DATE(date_days, 'DD/MM/YYYY');

代码解释 #

1. 日期转换：TO_DATE(t.Stardate, 'DD/MM/YYYY')将字符串格式的Stardate转为Oracle日期类型，方便后续日期运算。
2. 生成连续日期：LEVEL - 1生成0到6的偏移量，加到Stardate上得到一周的7天数据。
3. 星期名格式化：通过NLS_DATE_LANGUAGE=ENGLISH参数，确保星期名显示为英文（如Sunday），不受系统默认语言影响。
4. 层级查询控制：PRIOR t.ID = t.ID保证每条原记录对应生成7条单日数据，PRIOR SYS_GUID() IS NOT NULL防止出现笛卡尔积式的重复结果。
5. 排序逻辑：按ID和单日日期排序，保证结果顺序符合预期。

输出结果 #

执行后将得到如下结果：

注：如果你的Stardate字段本身就是DATE类型，可去掉TO_DATE(t.Stardate, 'DD/MM/YYYY')转换，直接用t.Stardate + LEVEL -1即可。

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