如何将多列值计数的SQL查询结果进行转置（Pivot）？

你的需求本质是行转列（Pivot），把纵向的分组统计结果转换成横向的行列结构。下面提供几种通用和数据库专属的实现方法，直接替换你的表名即可使用：

通用方案：条件聚合（兼容所有主流数据库） #

这是最稳妥的方式，不需要依赖数据库特定函数，用CASE WHEN配合聚合函数直接统计各状态数量：

假设你的原表中，risk、record、api是三个独立的状态字段（比如每行有risk_status、record_status、api_status），可以这样写：

SELECT
    'risk' AS nm_test,
    COUNT(CASE WHEN risk_status = 'OK' THEN 1 END) AS OK_count,
    COUNT(CASE WHEN risk_status = 'NO' THEN 1 END) AS NO_count
FROM your_table
UNION ALL
SELECT
    'record' AS nm_test,
    COUNT(CASE WHEN record_status = 'OK' THEN 1 END) AS OK_count,
    COUNT(CASE WHEN record_status = 'NO' THEN 1 END) AS NO_count
FROM your_table
UNION ALL
SELECT
    'api' AS nm_test,
    COUNT(CASE WHEN api_status = 'OK' THEN 1 END) AS OK_count,
    COUNT(CASE WHEN api_status = 'NO' THEN 1 END) AS NO_count
FROM your_table;

如果是同一个status字段对应不同的测试类型（比如通过test_type字段区分risk/record/api），可以简化成单表聚合：

SELECT
    test_type AS nm_test,
    COUNT(CASE WHEN status = 'OK' THEN 1 END) AS OK_count,
    COUNT(CASE WHEN status = 'NO' THEN 1 END) AS NO_count
FROM your_table
WHERE test_type IN ('risk', 'record', 'api')
GROUP BY test_type;

数据库专属方案（简化代码） #

如果你的数据库支持行转列专属函数，可以用更简洁的语法：

MySQL 8.0+/MariaDB 10.3+：PIVOT语法 #

WITH test_stats AS (
    SELECT 'risk' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
    UNION ALL
    SELECT 'record' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
    UNION ALL
    SELECT 'api' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
)
SELECT nm_test, `OK`, `NO`
FROM test_stats
PIVOT (SUM(cnt) FOR status IN (`OK`, `NO`)) AS pivot_result;

PostgreSQL：crosstab函数 #

需要先启用tablefunc扩展，再进行行转列：

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    'SELECT nm_test, status, COUNT(*) AS cnt
     FROM (
         SELECT ''risk'' AS nm_test, status FROM your_table
         UNION ALL
         SELECT ''record'' AS nm_test, status FROM your_table
         UNION ALL
         SELECT ''api'' AS nm_test, status FROM your_table
     ) AS combined_data
     GROUP BY nm_test, status
     ORDER BY nm_test, status',
    'VALUES (''OK''), (''NO'')'
) AS final_result(nm_test text, ok_count int, no_count int);

SQL Server：PIVOT语法 #

WITH test_stats AS (
    SELECT 'risk' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
    UNION ALL
    SELECT 'record' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
    UNION ALL
    SELECT 'api' AS nm_test, status, COUNT(*) AS cnt
    FROM your_table GROUP BY nm_test, status
)
SELECT nm_test, [OK], [NO]
FROM test_stats
PIVOT (SUM(cnt) FOR status IN ([OK], [NO])) AS pivot_result;

关键注意点 #

• 如果status字段除了OK/NO还有其他值，需要在CASE WHEN或PIVOT中补充枚举，或者用WHERE过滤掉无关值。
• 条件聚合方案兼容性最强，适合跨数据库场景；专属函数方案代码更简洁，但依赖数据库版本。

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