使用GROUP BY与CASE时消除零值显示的技术求助

你的SQL存在两个核心问题，导致不适用列显示0而非预期的空值：

1. 分组逻辑错误：
   按Item_Rating分组后，每一行只会对应一个评级值，其他列的CASE语句触发ELSE ''分支。但COUNT(Item_Rating)是整数类型，SQL会自动将空字符串''隐式转换为数值0，因此你看到的是0而非空值。

2. 数据类型不匹配：
   CASE语句中THEN分支返回整数（COUNT的结果），ELSE分支返回字符串，SQL会统一将结果转换为优先级更高的数值类型，空字符串转数值后就是0。

修正后的SQL代码 #

要实现每个客户一行、对应评级列显示统计数、不适用列显示空值的效果，需使用条件聚合，仅按Client_ID和Client_Name分组：

SELECT 
  Client_ID, 
  Client_Name, 
  CASE WHEN COUNT(CASE WHEN Item_Rating = '0' THEN 1 END) > 0 
       THEN COUNT(CASE WHEN Item_Rating = '0' THEN 1 END) 
       ELSE NULL END AS 'Non Compliant',
  CASE WHEN COUNT(CASE WHEN Item_Rating = '3' THEN 1 END) > 0 
       THEN COUNT(CASE WHEN Item_Rating = '3' THEN 1 END) 
       ELSE NULL END AS 'Partially Compliant',
  CASE WHEN COUNT(CASE WHEN Item_Rating = '5' THEN 1 END) > 0 
       THEN COUNT(CASE WHEN Item_Rating = '5' THEN 1 END) 
       ELSE NULL END AS 'Fully Compliant',
  CASE WHEN COUNT(CASE WHEN Item_Rating = '9' THEN 1 END) > 0 
       THEN COUNT(CASE WHEN Item_Rating = '9' THEN 1 END) 
       ELSE NULL END AS 'Incomplete',
  COUNT(Item_Rating) AS Total
FROM dbo.DigitalTrustFrameworkClient
GROUP BY Client_ID, Client_Name

如果需要返回空字符串而非NULL，可将统计结果转换为字符串类型：

SELECT 
  Client_ID, 
  Client_Name, 
  ISNULL(CAST(COUNT(CASE WHEN Item_Rating = '0' THEN 1 END) AS VARCHAR(10)), '') AS 'Non Compliant',
  ISNULL(CAST(COUNT(CASE WHEN Item_Rating = '3' THEN 1 END) AS VARCHAR(10)), '') AS 'Partially Compliant',
  ISNULL(CAST(COUNT(CASE WHEN Item_Rating = '5' THEN 1 END) AS VARCHAR(10)), '') AS 'Fully Compliant',
  ISNULL(CAST(COUNT(CASE WHEN Item_Rating = '9' THEN 1 END) AS VARCHAR(10)), '') AS 'Incomplete',
  COUNT(Item_Rating) AS Total
FROM dbo.DigitalTrustFrameworkClient
GROUP BY Client_ID, Client_Name

说明 #

• 条件聚合通过内层CASE标记符合条件的行，外层COUNT统计数量；无符合条件的行时COUNT返回0，再通过外层CASE或ISNULL转换为空值/空字符串。
• 移除Item_Rating分组后，每个客户仅生成一行数据，各列对应不同评级的统计结果。

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