Oracle数据库中如何将多行查询结果两两组合合并为单行？

原查询使用UNION会将Fall的start_date和Spring的end_date合并到同一列，并且自动去重，破坏了后续配对所需的结构，无法直接实现两两合并的需求。以下是两种可行的解决方案：

方案1：通过年份关联（适用于Fall和Spring年份连续的场景） #

假设Fall描述格式为Fall YYYY，对应Spring为Spring YYYY+1，可通过提取年份进行精准关联：

SELECT 
    f.fall_desc || ' - ' || s.spring_desc AS "DESC",
    f.start_date,
    s.end_date
FROM (
    -- 提取Fall类型记录及对应年份
    SELECT 
        desc AS fall_desc,
        start_date,
        TO_NUMBER(SUBSTR(desc, INSTR(desc, ' ') + 1)) AS year
    FROM foo.bar
    WHERE desc LIKE 'Fall%' AND desc NOT LIKE '%Med%'
) f
-- 关联对应年份+1的Spring记录
JOIN (
    SELECT 
        desc AS spring_desc,
        end_date,
        TO_NUMBER(SUBSTR(desc, INSTR(desc, ' ') + 1)) AS year
    FROM foo.bar
    WHERE desc LIKE 'Spring%' AND desc NOT LIKE '%Med%'
) s ON f.year + 1 = s.year
ORDER BY f.start_date;

方案2：通过行号配对（适用于Fall和Spring按顺序一一对应的场景） #

如果Fall和Spring记录按日期顺序一一出现，可使用窗口函数ROW_NUMBER()给两类记录分别编号，再通过编号配对：

WITH all_records AS (
    -- 提取Fall记录并按日期排序编号
    SELECT 
        desc,
        start_date AS date_val,
        'FALL' AS record_type,
        ROW_NUMBER() OVER (ORDER BY start_date) AS pair_id
    FROM foo.bar
    WHERE desc LIKE 'Fall%' AND desc NOT LIKE '%Med%'
    UNION ALL
    -- 提取Spring记录并按日期排序编号
    SELECT 
        desc,
        end_date AS date_val,
        'SPRING' AS record_type,
        ROW_NUMBER() OVER (ORDER BY end_date) AS pair_id
    FROM foo.bar
    WHERE desc LIKE 'Spring%' AND desc NOT LIKE '%Med%'
)
-- 按编号配对Fall和Spring记录
SELECT 
    fall_rec.desc || ' - ' || spring_rec.desc AS "DESC",
    fall_rec.date_val AS START_DATE,
    spring_rec.date_val AS END_DATE
FROM all_records fall_rec
JOIN all_records spring_rec 
    ON fall_rec.pair_id = spring_rec.pair_id
    AND fall_rec.record_type = 'FALL'
    AND spring_rec.record_type = 'SPRING'
ORDER BY fall_rec.date_val;

关键注意点 #

• 用UNION ALL替代UNION：避免UNION的自动去重逻辑丢失必要的配对记录。
• 关键字处理：desc是Oracle关键字，建议用双引号包裹（如"DESC"）或重命名字段，防止语法错误。

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