MySQL实现查询结果列转行：统计Product表状态字段空值/非空值

针对你的需求，以下是直接实现两种目标格式的SQL方案，包含基础版和性能优化版：

格式一：按空值状态分组，字段作为列 #

基础版（多次扫描表） #

SELECT 'Null' AS Status,
       COUNT(IF(Fav IS NULL, ID, NULL)) AS Fav,
       COUNT(IF(Purchase IS NULL, ID, NULL)) AS Purchase,
       COUNT(IF(Delivery IS NULL, ID, NULL)) AS Delivery
FROM Product
UNION ALL
SELECT 'Not-null' AS Status,
       COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS Fav,
       COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS Purchase,
       COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS Delivery
FROM Product;

优化版（仅扫描一次表） #

使用CTE（MySQL 8.0+支持）预先计算所有统计值，避免重复扫描表：

WITH stats AS (
    SELECT COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS Fav_NotNull,
           COUNT(IF(Fav IS NULL, ID, NULL)) AS Fav_Null,
           COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS Purchase_NotNull,
           COUNT(IF(Purchase IS NULL, ID, NULL)) AS Purchase_Null,
           COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS Delivery_NotNull,
           COUNT(IF(Delivery IS NULL, ID, NULL)) AS Delivery_Null
    FROM Product
)
SELECT 'Null' AS Status, Fav_Null AS Fav, Purchase_Null AS Purchase, Delivery_Null AS Delivery
FROM stats
UNION ALL
SELECT 'Not-null' AS Status, Fav_NotNull AS Fav, Purchase_NotNull AS Purchase, Delivery_NotNull AS Delivery
FROM stats;

执行后输出结果：

Status    Fav    Purchase    Delivery
Null      25     47          73
Not-null  75     53          27

格式二：按字段分组，空值状态作为列 #

基础版（多次扫描表） #

SELECT 'Fav' AS Field,
       COUNT(IF(Fav IS NULL, ID, NULL)) AS Null,
       COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS 'Not-null'
FROM Product
UNION ALL
SELECT 'Purchase' AS Field,
       COUNT(IF(Purchase IS NULL, ID, NULL)) AS Null,
       COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS 'Not-null'
FROM Product
UNION ALL
SELECT 'Delivery' AS Field,
       COUNT(IF(Delivery IS NULL, ID, NULL)) AS Null,
       COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS 'Not-null'
FROM Product;

优化版（仅扫描一次表） #

同样用CTE预先计算统计值，提升查询效率：

WITH stats AS (
    SELECT COUNT(IF(Fav IS NOT NULL, ID, NULL)) AS Fav_NotNull,
           COUNT(IF(Fav IS NULL, ID, NULL)) AS Fav_Null,
           COUNT(IF(Purchase IS NOT NULL, ID, NULL)) AS Purchase_NotNull,
           COUNT(IF(Purchase IS NULL, ID, NULL)) AS Purchase_Null,
           COUNT(IF(Delivery IS NOT NULL, ID, NULL)) AS Delivery_NotNull,
           COUNT(IF(Delivery IS NULL, ID, NULL)) AS Delivery_Null
    FROM Product
)
SELECT 'Fav' AS Field, Fav_Null AS Null, Fav_NotNull AS 'Not-null' FROM stats
UNION ALL
SELECT 'Purchase' AS Field, Purchase_Null AS Null, Purchase_NotNull AS 'Not-null' FROM stats
UNION ALL
SELECT 'Delivery' AS Field, Delivery_Null AS Null, Delivery_NotNull AS 'Not-null' FROM stats;

执行后输出结果：

Field     Null    Not-null
Fav       25      75
Purchase  47      53
Delivery  73      27

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