能否用PIVOT/UNPIVOT替代大量UNION实现数据统计格式转换？

你完全不需要写一堆重复的UNION语句，有几种更简洁的方式来实现需求，既能减少代码冗余，还能避免多次扫描数据表。

方法1：CTE+行构造器（VALUES）生成所有类别 #

先通过CTE计算总用户数，再用VALUES集中定义所有需要统计的类别及判断条件，最后一次性计算每个类别的总数和占比：

WITH TotalUsers AS (
    SELECT COUNT(*) AS total FROM UserData
)
SELECT
    category_name,
    SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS total,
    ROUND((SUM(CASE WHEN condition THEN 1 ELSE 0 END) * 100.0 / tu.total), 0) || '%' AS percentage
FROM TotalUsers tu
CROSS JOIN (
    VALUES
        ('总计', 1=1),
        ('男性', sex = 'M'),
        ('女性', sex = 'F'),
        ('其他/拒绝说明', sex NOT IN ('M', 'F')),
        ('本州', state = 'TX'), -- 此处假设本州为TX，可根据实际需求调整
        ('外州', state != 'TX'),
        ('黑人', race = 'B'),
        ('白人', race = 'W')
) AS categories(category_name, condition)
CROSS JOIN UserData ud
GROUP BY tu.total, category_name
ORDER BY 
    CASE category_name
        WHEN '总计' THEN 1
        WHEN '男性' THEN 2
        WHEN '女性' THEN 3
        WHEN '其他/拒绝说明' THEN 4
        WHEN '本州' THEN 5
        WHEN '外州' THEN 6
        WHEN '黑人' THEN 7
        WHEN '白人' THEN 8
    END;

该方法优势：

• 仅扫描一次UserData表，性能更优
• 所有类别定义集中在VALUES块，新增/修改类别只需调整此处，维护更便捷
• 统一计算占比，避免重复写除法逻辑

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

先计算各维度的聚合值，再通过UNPIVOT将列转成行：

WITH Stats AS (
    SELECT
        COUNT(*) AS total,
        SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS male,
        SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS female,
        SUM(CASE WHEN sex NOT IN ('M', 'F') THEN 1 ELSE 0 END) AS other_gender,
        SUM(CASE WHEN state = 'TX' THEN 1 ELSE 0 END) AS in_state,
        SUM(CASE WHEN state != 'TX' THEN 1 ELSE 0 END) AS out_state,
        SUM(CASE WHEN race = 'B' THEN 1 ELSE 0 END) AS black,
        SUM(CASE WHEN race = 'W' THEN 1 ELSE 0 END) AS white
    FROM UserData
)
SELECT
    category_name,
    category_value AS total,
    ROUND((category_value * 100.0 / total), 0) || '%' AS percentage
FROM Stats
UNPIVOT (
    category_value FOR category_name IN (
        total AS '总计',
        male AS '男性',
        female AS '女性',
        other_gender AS '其他/拒绝说明',
        in_state AS '本州',
        out_state AS '外州',
        black AS '黑人',
        white AS '白人'
    )
) AS unpvt
ORDER BY 
    CASE category_name
        WHEN '总计' THEN 1
        WHEN '男性' THEN 2
        WHEN '女性' THEN 3
        WHEN '其他/拒绝说明' THEN 4
        WHEN '本州' THEN 5
        WHEN '外州' THEN 6
        WHEN '黑人' THEN 7
        WHEN '白人' THEN 8
    END;

该方法适合先批量计算聚合值再转结构的场景，代码结构清晰。

为什么不推荐大量UNION？ #

• 多次扫描数据表，数据量大时性能低下
• 代码重复度高，修改逻辑需调整多个UNION块，维护成本高
• 易出现拼写错误或逻辑不一致问题

原始数据 #

目标结果 #

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