优化查询同一ID下存在不同值列的SQL语句

原查询通过多次GROUP BY结合UNION实现需求，但这种方式会对原表进行多次全表扫描，当数据量较大时性能会显著下降。以下是几种更高效的优化方案：

方案1：一次分组后通过数组转多行（适用于PostgreSQL、BigQuery等） #

先对ID分组，一次性计算所有列的差异状态，再通过数组函数将有差异的列转为行记录，仅需扫描原表一次：

SELECT 
    t.ID,
    unnest(array_remove(array['Brand', 'Type'], NULL)) AS Discrepancy
FROM (
    SELECT 
        ID,
        CASE WHEN COUNT(DISTINCT Brand) > 1 THEN 'Brand' ELSE NULL END AS brand_diff,
        CASE WHEN COUNT(DISTINCT Type) > 1 THEN 'Type' ELSE NULL END AS type_diff
    FROM table
    GROUP BY ID
) t
WHERE brand_diff IS NOT NULL OR type_diff IS NOT NULL

方案2：使用UNPIVOT（适用于SQL Server、Oracle等） #

利用数据库内置的行转列函数UNPIVOT，基于一次分组的结果生成差异列记录：

SELECT ID, Discrepancy
FROM (
    SELECT 
        ID,
        CASE WHEN COUNT(DISTINCT Brand) > 1 THEN 1 ELSE 0 END AS Brand,
        CASE WHEN COUNT(DISTINCT Type) > 1 THEN 1 ELSE 0 END AS Type
    FROM table
    GROUP BY ID
) src
UNPIVOT (
    HasDiscrepancy FOR Discrepancy IN (Brand, Type)
) unpvt
WHERE HasDiscrepancy = 1

方案3：通用CTE+UNION ALL #

通过CTE先完成一次分组计算所有列的差异计数，再用UNION ALL生成结果，相比原方案减少了表扫描次数：

WITH grouped AS (
    SELECT 
        ID,
        COUNT(DISTINCT Brand) AS brand_distinct,
        COUNT(DISTINCT Type) AS type_distinct
    FROM table
    GROUP BY ID
)
SELECT ID, 'Brand' AS Discrepancy FROM grouped WHERE brand_distinct > 1
UNION ALL
SELECT ID, 'Type' AS Discrepancy FROM grouped WHERE type_distinct > 1

额外优化建议 #

• 替换UNION为UNION ALL：原查询中UNION会自动去重，但不同列的Discrepancy值唯一，无需去重，UNION ALL性能更优。
• 添加复合索引：针对(ID, Brand)、(ID, Type)创建复合索引，可加速COUNT(DISTINCT)的计算，避免回表查询。

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