如何优化含多CASE WHEN语句的SQL查询以提升执行效率？

我编写的SQL查询包含多个CASE WHEN语句，每个CASE里重复执行相同的子查询，导致代码运行耗时过长，希望找到更优的写法来优化该查询。

原查询代码：

SELECT equipment_id 

, CASE  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '3qwe' 
  THEN 'Table 1.1-2, ' 
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '6qwe' 
  THEN 'Table 1.2-2, ' 
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '6qre' 
  THEN 'Table 1.2-3, '
  ELSE NULL 
  END table_number
  
, CASE  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 14 AND equipment_id = e.equipment_id) = 'gas' 
  THEN 'Gas Powered ' 
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 14 AND equipment_id = e.equipment_id) = 'electric' 
  THEN 'Electric Powered '
  ELSE NULL 
  END power_type
  
, CASE  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '3qwe' 
  THEN '3-round, west' 
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '6qwe' 
  THEN '6 round, west '  
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = '6qre' 
  THEN '6-round, east' 
  WHEN (SELECT LOWER(to_char(equipment_type)) FROM equipment_type WHERE equipment_type_id = 100001 AND equipment_id = e.equipment_id) = 'n/a'    
  THEN '(<= 200 mg)' 
  ELSE NULL 
  END  rating_class

 
FROM equipment e

优化方案 #

1. 提前关联数据，避免重复子查询

将重复查询的equipment_type数据通过LEFT JOIN一次性取出，每个equipment_id只查询一次对应类型的数据，大幅减少数据库IO操作：

SELECT 
    e.equipment_id,
    CASE 
        WHEN et1.type_value = '3qwe' THEN 'Table 1.1-2, '
        WHEN et1.type_value = '6qwe' THEN 'Table 1.2-2, '
        WHEN et1.type_value = '6qre' THEN 'Table 1.2-3, '
        ELSE NULL 
    END table_number,
    CASE 
        WHEN et2.type_value = 'gas' THEN 'Gas Powered '
        WHEN et2.type_value = 'electric' THEN 'Electric Powered '
        ELSE NULL 
    END power_type,
    CASE 
        WHEN et1.type_value = '3qwe' THEN '3-round, west'
        WHEN et1.type_value = '6qwe' THEN '6 round, west '
        WHEN et1.type_value = '6qre' THEN '6-round, east'
        WHEN et1.type_value = 'n/a' THEN '(<= 200 mg)'
        ELSE NULL 
    END rating_class
FROM equipment e
LEFT JOIN (
    SELECT 
        equipment_id,
        LOWER(to_char(equipment_type)) AS type_value
    FROM equipment_type
    WHERE equipment_type_id = 100001
) et1 ON e.equipment_id = et1.equipment_id
LEFT JOIN (
    SELECT 
        equipment_id,
        LOWER(to_char(equipment_type)) AS type_value
    FROM equipment_type
    WHERE equipment_type_id = 14
) et2 ON e.equipment_id = et2.equipment_id;

2. 使用CROSS APPLY简化关联（适用于SQL Server、PostgreSQL等）

通过APPLY子查询一次性获取当前equipment_id对应的两种类型值，减少关联次数：

SELECT 
    e.equipment_id,
    CASE 
        WHEN et.type_100001 = '3qwe' THEN 'Table 1.1-2, '
        WHEN et.type_100001 = '6qwe' THEN 'Table 1.2-2, '
        WHEN et.type_100001 = '6qre' THEN 'Table 1.2-3, '
        ELSE NULL 
    END table_number,
    CASE 
        WHEN et.type_14 = 'gas' THEN 'Gas Powered '
        WHEN et.type_14 = 'electric' THEN 'Electric Powered '
        ELSE NULL 
    END power_type,
    CASE 
        WHEN et.type_100001 = '3qwe' THEN '3-round, west'
        WHEN et.type_100001 = '6qwe' THEN '6 round, west '
        WHEN et.type_100001 = '6qre' THEN '6-round, east'
        WHEN et.type_100001 = 'n/a' THEN '(<= 200 mg)'
        ELSE NULL 
    END rating_class
FROM equipment e
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN equipment_type_id = 100001 THEN LOWER(to_char(equipment_type)) END) AS type_100001,
        MAX(CASE WHEN equipment_type_id = 14 THEN LOWER(to_char(equipment_type)) END) AS type_14
    FROM equipment_type et
    WHERE et.equipment_id = e.equipment_id
      AND et.equipment_type_id IN (14, 100001)
) et;

3. 添加索引提升查询效率

在equipment_type表上创建复合索引，让数据库能快速定位目标数据：

CREATE INDEX idx_equipment_type_id_eqid ON equipment_type(equipment_type_id, equipment_id, equipment_type);

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