Oracle中如何高效在SELECT语句中基于另一表值存在性新增列/变量

原代码因多次执行EXISTS子查询导致重复扫描errors表，效率低下。以下是两种高效改写方案及配套优化措施：

方案一：条件聚合（通用所有SQL数据库） #

通过一次关联+条件聚合完成行转列，仅扫描errors表一次，大幅降低IO开销：

SELECT 
    f.detail_id,
    MAX(CASE WHEN e.error_code = 400 THEN 1 ELSE 0 END) AS error_400,
    MAX(CASE WHEN e.error_code = 405 THEN 1 ELSE 0 END) AS error_405,
    MAX(CASE WHEN e.error_code = 410 THEN 1 ELSE 0 END) AS error_410,
    MAX(CASE WHEN e.error_code = 392 THEN 1 ELSE 0 END) AS error_392,
    MAX(CASE WHEN e.error_code = 401 THEN 1 ELSE 0 END) AS error_401
FROM files f
LEFT JOIN errors e ON f.detail_id = e.detail_id
GROUP BY f.detail_id

逻辑说明：LEFT JOIN确保所有files表的detail_id都被保留，MAX(CASE...)判断当前detail_id是否存在对应错误码，存在则返回1，否则0。

方案二：使用PIVOT语法（适用于SQL Server、Oracle等支持PIVOT的数据库） #

利用数据库内置行转列语法，代码更简洁，执行效率与条件聚合一致：

-- SQL Server 示例
SELECT detail_id, 
       ISNULL([400], 0) AS error_400,
       ISNULL([405], 0) AS error_405,
       ISNULL([410], 0) AS error_410,
       ISNULL([392], 0) AS error_392,
       ISNULL([401], 0) AS error_401
FROM (
    SELECT f.detail_id, e.error_code
    FROM files f
    LEFT JOIN errors e ON f.detail_id = e.detail_id
) AS src
PIVOT (
    COUNT(error_code) -- 用COUNT判断错误码是否存在
    FOR error_code IN ([400], [405], [410], [392], [401])
) AS pvt

逻辑说明：子查询先关联两张表，PIVOT将错误码列转成哑编码字段，ISNULL把NULL值替换为0，保证结果格式统一。

关键索引优化 #

无论使用哪种方案，添加以下索引可进一步提升性能：

• 在errors表创建复合索引：

  CREATE INDEX idx_errors_detail_code ON errors(detail_id, error_code);
  

  该索引能让关联和错误码过滤直接走索引，避免回表扫描。
• 确保files表的detail_id为主键或存在唯一索引，加速关联匹配。

原代码低效原因 #

原代码中每个CASE语句都会触发一次独立的EXISTS子查询，相当于对errors表执行5次重复扫描。当数据量较大时，重复扫描的IO开销会呈线性增长，导致查询耗时剧增。优化后的方案仅需扫描errors表一次，性能提升显著。

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