Oracle中解决子查询返回多行值及实现指定行转列的方法

一、行转列需求实现 #

原始数据表 #

ID      NAME       ROLE
1       KONDA      LEAD
1       SATHI      CO-LEAD
1       JOHN       CO-LEAD
2       REDDY      LEAD
2       SURESH     CO-LEAD
3       PRASAD     LEAD

目标结果表 #

ID      LEAD       CO-LEAD_1       CO-LEAD_2
1       KONDA      SATHI           JOHN
2       REDDY      SURESH
3       PRASAD

实现方案：使用Oracle PIVOT函数 #

通过PIVOT结合行号生成，可实现静态列的行转列，SQL语句如下：

WITH ranked_data AS (
    SELECT 
        ID,
        NAME,
        ROLE,
        -- 为每个ID下的CO-LEAD生成序号
        ROW_NUMBER() OVER(PARTITION BY ID, ROLE ORDER BY NAME) AS rn
    FROM your_table_name
)
SELECT 
    ID,
    LEAD,
    CO_LEAD_1 AS "CO-LEAD_1",
    CO_LEAD_2 AS "CO-LEAD_2"
FROM ranked_data
PIVOT (
    MAX(NAME) FOR (ROLE, rn) IN (
        ('LEAD', 1) AS LEAD,
        ('CO-LEAD', 1) AS CO_LEAD_1,
        ('CO-LEAD', 2) AS CO_LEAD_2
    )
)
ORDER BY ID;

如果CO-LEAD的数量不确定，可通过动态SQL生成对应列，上述静态SQL适用于已知最大数量的场景。

二、解决Oracle子查询返回多行的问题 #

当子查询返回多行时，直接使用=会触发报错，以下是几种可行处理方案：

• 使用IN关键字：若主查询需要匹配子查询返回的任意值，用IN替代=，示例：

  SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE loc = 'NY');
  

• 使用ANY/ALL关键字：

  • ANY表示匹配子查询返回的任意一个值，示例：sal > ANY(SELECT sal FROM emp WHERE dept_id=10)
  • ALL表示匹配子查询返回的所有值，示例：sal > ALL(SELECT sal FROM emp WHERE dept_id=10)

• 用聚合函数将多行转单行：若需要子查询返回单个值，使用MAX()/MIN()/SUM()等聚合函数，示例：

  SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp WHERE dept_id=10);
  

• 使用EXISTS替代子查询：仅需判断存在性时，EXISTS效率更高，示例：

  SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id AND d.loc='NY');
  

• 将多行子查询转为关联查询：把子查询改为JOIN，避免单行匹配限制，示例：

  SELECT e.* FROM emp e
  JOIN (SELECT dept_id FROM dept WHERE loc='NY') d
  ON e.dept_id = d.dept_id;
  

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