统计总ID数、含任意空值ID数及占比的SQL实现咨询

DevOps 小满

2026-9-24

原有SQL的问题

你编写的SQL存在两个核心错误：

用union all分别统计三个字段的空值ID时，同一个ID如果多列都为空会被重复计数，比如示例中的ID 2Y会被算3次，导致统计的空值ID数量虚高
最终百分比计算逻辑错误，且没有做百分比格式化处理，和预期输出的格式不符

最优实现方案

思路：先关联两张表，对每个ID判断Name、City、State三个字段是否至少有一个为空，再做聚合统计即可，避免重复计数。
通用SQL写法如下：

SELECT
  COUNT(*) AS Total_Count,
  SUM(CASE WHEN a.Name IS NULL OR b.City IS NULL OR b.State IS NULL THEN 1 ELSE 0 END) AS `Ids having null`,
  CONCAT(ROUND(SUM(CASE WHEN a.Name IS NULL OR b.City IS NULL OR b.State IS NULL THEN 1 ELSE 0 END)/COUNT(*)*100, 2), '%') AS `Percentage missing`
FROM TableA a
JOIN TableB b ON a.ID = b.B_ID;

如果是MySQL/PostgreSQL等支持IF语法的数据库，可以简化判断逻辑：

SELECT
  COUNT(*) AS Total_Count,
  SUM(IF(a.Name IS NULL OR b.City IS NULL OR b.State IS NULL, 1, 0)) AS `Ids having null`,
  CONCAT(ROUND(SUM(IF(a.Name IS NULL OR b.City IS NULL OR b.State IS NULL, 1, 0))/COUNT(*)*100, 2), '%') AS `Percentage missing`
FROM TableA a
INNER JOIN TableB b ON a.ID = b.B_ID;