如何优化含多子查询的CASE WHEN语句的SQL查询

原查询通过嵌套子查询关联equipment和equipment_feature表（单设备对应多特性），虽能按equipment_id单行返回结果，但多次子查询导致性能低下。改用JOIN后出现同一equipment_id多行记录的问题，以下是两种优化方案，既能保证结果与原查询一致，又能大幅提升运行效率。

原查询代码 #

SELECT equipment_id 
, CASE  WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '2ea' 
THEN 'Table 3.2-1, ' 
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '4ea' 
THEN 'Table 3.2-2, ' 
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '4so' 
THEN 'Table 3.2-3, '
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = 'n/a' 
THEN 'Table 3.3-1, '
ELSE NULL 
END direction_table
, CASE  WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 62 AND equipment_id = e.equipment_id) = 'gas' 
THEN 'Gas Powered '   
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 62 AND equipment_id = e.equipment_id) = 'electric' 
THEN 'Electric Powered '
ELSE NULL 
END fuel_type
, CASE  WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '2ea' 
THEN '2 East' 
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '4ea' 
THEN '4 East'  
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = '4so' 
THEN '4 South' 
WHEN (SELECT LOWER(to_char(equipment_feature)) FROM equipment_feature WHERE feature_type_id = 100001 AND equipment_id = e.equipment_id) = 'n/a'    
THEN '(<= 300 hp)' 
ELSE NULL 
END  rating_class
from equipment e

修改后（存在多行问题）的查询代码 #

SELECT e.equipment_id
, CASE WHEN LOWER(to_char(equipment_feature)) = '2ea' and feature_type_id = 100001  
THEN 'Table 3.2-1, ' 
WHEN LOWER(to_char(equipment_feature)) = '4ea' and feature_type_id = 100001
THEN 'Table 3.2-2, ' 
WHEN LOWER(to_char(equipment_feature)) = '4so' and feature_type_id = 100001 
THEN 'Table 3.2-3, '
WHEN LOWER(to_char(equipment_feature)) = 'n/a' and feature_type_id = 100001 
THEN 'Table 3.3-1, '
ELSE NULL 
END direction_table  
, CASE  WHEN LOWER(to_char(equipment_feature)) = 'gas' and feature_type_id = 62 
THEN 'Gas Powered '   
WHEN LOWER(to_char(equipment_feature))= 'electric' and feature_type_id = 62
THEN 'Electric Powered '
ELSE NULL 
END fuel_type  
, CASE  WHEN LOWER(to_char(equipment_feature)) = '2ea' and feature_type_id = 100001 
THEN '2 East' 
WHEN LOWER(to_char(equipment_feature)) = '4ea' and feature_type_id = 100001 
THEN '4 East'  
WHEN LOWER(to_char(equipment_feature)) = '4so' and feature_type_id = 100001
THEN '4 South' 
WHEN LOWER(to_char(equipment_feature)) = 'n/a' and feature_type_id = 100001  
THEN '(<= 300 hp)' 
ELSE NULL 
END  rating_class
from equipment e
inner join equipment_feature ea on e.EQUIPMENT_ID = ea.EQUIPMENT_id

优化方案1：条件聚合（通用兼容型） #

通过MAX()聚合函数结合CASE语句，将同一设备的多行特性合并为单行，同时仅关联一次equipment_feature表，避免原查询的重复扫描。

SELECT 
    e.equipment_id,
    -- 生成direction_table字段
    MAX(CASE WHEN ea.feature_type_id = 100001 THEN
        CASE LOWER(TO_CHAR(ea.equipment_feature))
            WHEN '2ea' THEN 'Table 3.2-1, '
            WHEN '4ea' THEN 'Table 3.2-2, '
            WHEN '4so' THEN 'Table 3.2-3, '
            WHEN 'n/a' THEN 'Table 3.3-1, '
            ELSE NULL
        END
    END) AS direction_table,
    -- 生成fuel_type字段
    MAX(CASE WHEN ea.feature_type_id = 62 THEN
        CASE LOWER(TO_CHAR(ea.equipment_feature))
            WHEN 'gas' THEN 'Gas Powered '
            WHEN 'electric' THEN 'Electric Powered '
            ELSE NULL
        END
    END) AS fuel_type,
    -- 生成rating_class字段
    MAX(CASE WHEN ea.feature_type_id = 100001 THEN
        CASE LOWER(TO_CHAR(ea.equipment_feature))
            WHEN '2ea' THEN '2 East'
            WHEN '4ea' THEN '4 East'
            WHEN '4so' THEN '4 South'
            WHEN 'n/a' THEN '(<= 300 hp)'
            ELSE NULL
        END
    END) AS rating_class
FROM equipment e
LEFT JOIN equipment_feature ea 
    ON e.equipment_id = ea.equipment_id
    AND ea.feature_type_id IN (62, 100001) -- 仅加载需要的特性类型，减少数据量
GROUP BY e.equipment_id;

方案优势 #

• 仅扫描equipment_feature表一次，远低于原查询多次子查询的扫描次数
• 用MAX()过滤NULL值，确保同一设备仅返回一行
• 提前过滤不需要的特性类型，减少内存处理的数据量

优化方案2：分特性关联（适合支持多表关联的数据库） #

提前筛选所需特性，通过多次LEFT JOIN分别关联不同类型的特性数据，确保每行对应一个设备。

WITH filtered_features AS (
    SELECT 
        equipment_id,
        feature_type_id,
        LOWER(TO_CHAR(equipment_feature)) AS feature_value
    FROM equipment_feature
    WHERE feature_type_id IN (62, 100001)
)
SELECT 
    e.equipment_id,
    -- 映射direction_table
    CASE ft100001.feature_value
        WHEN '2ea' THEN 'Table 3.2-1, '
        WHEN '4ea' THEN 'Table 3.2-2, '
        WHEN '4so' THEN 'Table 3.2-3, '
        WHEN 'n/a' THEN 'Table 3.3-1, '
        ELSE NULL
    END AS direction_table,
    -- 映射fuel_type
    CASE ft62.feature_value
        WHEN 'gas' THEN 'Gas Powered '
        WHEN 'electric' THEN 'Electric Powered '
        ELSE NULL
    END AS fuel_type,
    -- 映射rating_class
    CASE ft100001.feature_value
        WHEN '2ea' THEN '2 East'
        WHEN '4ea' THEN '4 East'
        WHEN '4so' THEN '4 South'
        WHEN 'n/a' THEN '(<= 300 hp)'
        ELSE NULL
    END AS rating_class
FROM equipment e
LEFT JOIN filtered_features ft100001 
    ON e.equipment_id = ft100001.equipment_id 
    AND ft100001.feature_type_id = 100001
LEFT JOIN filtered_features ft62 
    ON e.equipment_id = ft62.equipment_id 
    AND ft62.feature_type_id = 62;

方案优势 #

• 逻辑清晰，每个特性类型的映射独立处理
• CTE预过滤数据，避免重复筛选
• LEFT JOIN保证即使设备无对应特性，仍能返回该行（NULL值）

额外性能优化建议 #

在equipment_feature表上创建复合索引：

CREATE INDEX idx_equipment_feature_type ON equipment_feature (equipment_id, feature_type_id, equipment_feature);

该索引可让查询直接从索引中获取所需数据，无需回表扫描，大幅提升关联效率。

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