替换Union All为Join优化SQL查询性能：激励表转置查询提速需求

首先，从你的表结构和期望输出来看，你是要把宽表格式的激励数据转换成窄表（行转列），原查询耗时20分钟大概率是因为多次扫描全表导致的IO开销过大。下面给你几个高效的优化方案：

方案1：单次扫描表+CASE表达式（通用所有数据库） #

这个方法只需要扫描一次表，避免了多次扫描的开销，同时过滤掉无激励的行：

SELECT 
    Transaction_ID,
    CASE
        WHEN Incentive_On_A > 0 THEN 'A'
        WHEN Incentive_On_B > 0 THEN 'B'
        WHEN Incentive_On_C > 0 THEN 'C'
    END AS Product_Category,
    CASE
        WHEN Incentive_On_A > 0 THEN Incentive_On_A
        WHEN Incentive_On_B > 0 THEN Incentive_On_B
        WHEN Incentive_On_C > 0 THEN Incentive_On_C
    END AS Incentive_Amt
FROM Incentives
WHERE 
    Incentive_On_A > 0 
    OR Incentive_On_B > 0 
    OR Incentive_On_C > 0

为什么快？ #

• 只扫描一次表，把三次IO操作压缩成一次，数据量越大效果越明显
• WHERE条件提前过滤掉全0的无效行，减少后续处理的数据量

方案2：用UNPIVOT语法（适用于SQL Server/Oracle等支持的数据库） #

很多数据库提供了专门的行转列语法UNPIVOT，引擎对这个语法有原生优化，性能同样出色：

SELECT 
    Transaction_ID,
    Product_Category,
    Incentive_Amt
FROM Incentives
UNPIVOT (
    Incentive_Amt FOR Product_Category IN (
        [Incentive_On_A] AS 'A',
        [Incentive_On_B] AS 'B',
        [Incentive_On_C] AS 'C'
    )
) AS UnpivotedData
WHERE Incentive_Amt > 0

为什么快？ #

• 原生语法，数据库引擎会生成最优执行计划，避免手动UNION的冗余操作
• 同样只扫描一次表，逻辑更简洁

额外性能提升建议 #

如果数据量特别大（比如千万级以上），可以再加上这些优化：

• 创建过滤索引：如果大部分行都是全0的激励数据，创建一个只包含非0行的索引，查询时直接走索引，不用扫全表：

-- SQL Server示例，其他数据库语法类似
CREATE NONCLUSTERED INDEX IX_Incentives_NonZero
ON Incentives (Transaction_ID)
INCLUDE (Incentive_On_A, Incentive_On_B, Incentive_On_C)
WHERE Incentive_On_A > 0 OR Incentive_On_B > 0 OR Incentive_On_C > 0;

• 避免不必要的列：确保查询只返回你需要的Transaction_ID、Product_Category、Incentive_Amt，不要SELECT多余列，减少数据传输开销
• 分区表（可选）：如果表数据量超大规模，可以按Transaction_ID或时间范围（如果有时间列的话）做分区，进一步缩小扫描范围

原查询慢的核心原因 #

猜测你原来的查询是用了三次SELECT+UNION的组合，这种写法会扫描表三次，每次都要读取全表数据，IO开销是单次扫描的3倍；如果用的是UNION而不是UNION ALL，还会额外增加去重的计算开销，这就是耗时20分钟的主要原因。

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