Oracle 12c中如何无需重复子查询将多列转为相邻列对UNION结果？

当然可以做到！在Oracle 12c版本中，你完全不需要重复编写投影a、b、c、d的子查询，下面提供几种简洁高效的方案来完成这个结果重塑操作：

方案1：使用LATERAL关联子查询（推荐，直观高效） #

Oracle 12c引入了LATERAL关键字，允许关联子查询引用外部查询的列，这样我们只需要定义一次原始数据查询，就能生成所有需要的属性对：

WITH original_data AS (
    -- 替换成你的实际查询，只需要写一次
    SELECT a, b, c, d FROM your_target_table
)
SELECT x, y
FROM original_data,
LATERAL (
    SELECT a AS x, b AS y FROM dual
    UNION ALL  -- 若需去重可替换为UNION
    SELECT b AS x, c AS y FROM dual
    UNION ALL
    SELECT c AS x, d AS y FROM dual
);

方案说明 #

• LATERAL子查询会对original_data中的每一行执行一次，直接引用该行的a、b、c、d列生成属性对
• 用UNION ALL替代UNION可以避免不必要的去重操作，提升查询效率；如果你的场景需要去重，再换成UNION即可

方案2：使用XMLTABLE拆分属性对（适合多属性场景） #

如果你的属性数量较多，不想写多个UNION ALL，可以通过构造XML文档再解析的方式实现：

WITH original_data AS (
    SELECT a, b, c, d FROM your_target_table
)
SELECT x, y
FROM original_data,
XMLTABLE(
    'ROWSET/ROW'
    PASSING XMLTYPE(
        '<ROWSET>' ||
        '<ROW><X>' || a || '</X><Y>' || b || '</Y></ROW>' ||
        '<ROW><X>' || b || '</X><Y>' || c || '</Y></ROW>' ||
        '<ROW><X>' || c || '</X><Y>' || d || '</Y></ROW>' ||
        '</ROWSET>'
    )
    COLUMNS
        x VARCHAR2(100) PATH 'X',  -- 根据实际数据类型调整长度
        y VARCHAR2(100) PATH 'Y'
);

方案说明 #

• 通过拼接XML字符串，将每个属性对封装为独立的<ROW>节点
• XMLTABLE负责将XML数据转换为关系型结果集
• 如果a、b、c、d是不同数据类型，建议用TO_CHAR()统一转换，避免类型不匹配错误

方案3：UNPIVOT+自连接（灵活适配多属性扩展） #

如果后续可能需要增加更多属性，这种方案扩展性更强，无需修改UNION部分：

WITH original_data AS (
    -- 生成唯一标识id，若原表有主键可直接使用主键
    SELECT ROWNUM AS id, a, b, c, d FROM your_target_table
),
unpivoted_data AS (
    SELECT 
        id,
        col_value,
        -- 按属性顺序生成序号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY col_seq) AS seq
    FROM original_data
    UNPIVOT (
        col_value FOR (col_name, col_seq) IN (
            a AS ('a', 1), 
            b AS ('b', 2), 
            c AS ('c', 3), 
            d AS ('d', 4)
        )
    )
)
SELECT 
    u1.col_value AS x,
    u2.col_value AS y
FROM unpivoted_data u1
JOIN unpivoted_data u2 
    ON u1.id = u2.id 
    AND u1.seq + 1 = u2.seq;

方案说明 #

• 先用UNPIVOT将列转换为行，同时为每个属性指定顺序序号
• 通过自连接匹配序号相邻的行，得到连续的属性对
• 后续新增属性时，只需要在UNPIVOT的IN子句中添加新的属性和序号即可

注意事项 #

• 确保所有属性对的数据类型一致，若不一致可通过CAST()或TO_CHAR()转换
• 优先选择UNION ALL而非UNION，除非确实需要去重，前者性能更优

内容的提问来源于stack exchange，提问作者Jindřich Mynarz