如何使用SQL Pivot统计非空列数量，找出填充列最多的记录

首先纠正一个认知误区：你这个逐行统计非空列数的需求不需要使用PIVOT子句即可实现，PIVOT的核心能力是行转列，用在这个场景属于过度设计，反而会增加代码复杂度。下面先给最优实现方案，再补充行列转换相关的实现方式供你学习参考。

前置假设 #

假设你的5列表名为test_table，5个业务列分别为col1、col2、col3、col4、col5，表中存在唯一主键列id。
你提到的数据示例图如下：

方案1：直接逐列统计（生产环境最优） #

这个方案兼容性最强、性能最高，所有SQL数据库都可以使用：

SELECT
  *,
  -- 逐列判断非空计数后求和
  (CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END
  + CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END
  + CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END
  + CASE WHEN col4 IS NOT NULL THEN 1 ELSE 0 END
  + CASE WHEN col5 IS NOT NULL THEN 1 ELSE 0 END) AS non_null_count
FROM test_table
-- 直接排序取非空列最多的第一条记录
ORDER BY non_null_count DESC
LIMIT 1;

部分数据库有内置简化函数可以进一步缩短代码：

• PostgreSQL 可以直接用num_nonnulls(col1,col2,col3,col4,col5)替代整段CASE判断
• MySQL 可以简化为(col1 IS NOT NULL)+(col2 IS NOT NULL)+(col3 IS NOT NULL)+(col4 IS NOT NULL)+(col5 IS NOT NULL)，因为数据库会自动把布尔判断结果转为1/0数值

方案2：基于UNPIVOT实现（仅作学习参考） #

如果你要练习行列转换语法，可以参考这个实现逻辑：先通过UNPIVOT把每行的列转为行记录（自动过滤NULL值），统计每行非空数后再关联回原表，硬要加PIVOT逻辑只会更冗余，完全没有必要。

WITH unpivot_result AS (
  -- UNPIVOT会自动过滤NULL值，剩下的就是每行的非空列
  SELECT id, col_val
  FROM test_table
  UNPIVOT (
    col_val FOR col_name IN (col1,col2,col3,col4,col5)
  ) AS up
),
non_null_calc AS (
  SELECT id, COUNT(*) AS non_null_count
  FROM unpivot_result
  GROUP BY id
)
SELECT t.*, nc.non_null_count
FROM test_table t
LEFT JOIN non_null_calc nc ON t.id = nc.id
ORDER BY nc.non_null_count DESC
LIMIT 1;

这个方案需要表有唯一主键，性能比方案1差，仅适合学习语法使用。

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