如何通过SQL透视获取各费用列的COUNT、MIN、MAX统计结果？

要实现以列名为行、统计项（COUNT/MIN/MAX）为列的结果，可以通过先拆分行（UNION ALL）再透视列的方式完成，以下是具体实现：

通用兼容方案（适用于所有关系型数据库） #

用UNION ALL将每列的三个统计值拆分为行数据，再通过条件聚合将统计项转为列：

SELECT 
    column_name,
    MAX(CASE WHEN stat_type = 'COUNT' THEN stat_value END) AS COUNT,
    MAX(CASE WHEN stat_type = 'MIN' THEN stat_value END) AS MIN,
    MAX(CASE WHEN stat_type = 'MAX' THEN stat_value END) AS MAX
FROM (
    -- 拆分CHRG_ACCOM列的统计值
    SELECT 'CHRG_ACCOM' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_ACCOM) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ACCOM' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_ACCOM) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ACCOM' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_ACCOM) AS stat_value FROM expense_table
    -- 拆分CHRG_ANCIL列的统计值
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_ANCIL) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_ANCIL) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_ANCIL) AS stat_value FROM expense_table
    -- 拆分CHRG_TOT列的统计值
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_TOT) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_TOT) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_TOT) AS stat_value FROM expense_table
) AS unpivoted_data
GROUP BY column_name;

专用PIVOT语法方案（适用于SQL Server、Oracle等） #

如果使用支持PIVOT语法的数据库，可以简化透视部分的代码：

SELECT column_name, [COUNT], [MIN], [MAX]
FROM (
    SELECT 'CHRG_ACCOM' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_ACCOM) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ACCOM' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_ACCOM) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ACCOM' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_ACCOM) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_ANCIL) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_ANCIL) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_ANCIL' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_ANCIL) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'COUNT' AS stat_type, 
           COUNT(CHRG_TOT) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'MIN' AS stat_type, 
           MIN(CHRG_TOT) AS stat_value FROM expense_table
    UNION ALL
    SELECT 'CHRG_TOT' AS column_name, 
           'MAX' AS stat_type, 
           MAX(CHRG_TOT) AS stat_value FROM expense_table
) AS source_data
PIVOT (
    MAX(stat_value)
    FOR stat_type IN ([COUNT], [MIN], [MAX])
) AS pivot_table;

说明 #

• COUNT(column_name)统计的是该列非空值的行数，如果需要统计全表行数（包含NULL），可以替换为COUNT(*)
• 通用方案的兼容性更强，无需依赖数据库专用语法

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