按美国州编码拆分SQL多条件统计结果的实现问询

不需要用WHILE循环或复杂的CASE WHEN嵌套，直接用GROUP BY子句就能按州编码分组统计，这是SQL处理分组统计最直接高效的方式。

假设你的州编码列名为State_Code，修改后的SQL如下：

select 
    State_Code,
    count(Col_A) as Col_A_Count,
    count(Col_B) as Col_B_Count, 
    count(case when (Col_B = '0' or Col_B is NULL or Col_B = '-' or Col_B = 'NA' or Col_B = '') then 1 else null end) as Zero_Col_B_Count
from 
    你的数据表名
group by 
    State_Code;

关键说明： #

• GROUP BY State_Code会将数据按州编码分组，每组单独计算对应统计值
• count(Col_A)自动忽略该列的NULL值，统计非空行数
• count(Col_B)同理，仅统计Col_B非空的行数
• 原有的Zero_Col_B_Count逻辑保留，会统计每组中Col_B属于未填充情况的行数

如果需要同时统计所有州的合计行，可以用ROLLUP扩展实现：

select 
    coalesce(State_Code, '总计') as State_Code,
    count(Col_A) as Col_A_Count,
    count(Col_B) as Col_B_Count, 
    count(case when (Col_B = '0' or Col_B is NULL or Col_B = '-' or Col_B = 'NA' or Col_B = '') then 1 else null end) as Zero_Col_B_Count
from 
    你的数据表名
group by 
    rollup(State_Code);

rollup(State_Code)会在结果末尾添加一行总计，coalesce用来把NULL替换成“总计”字样，提升结果可读性。

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