如何用T-SQL统计值大于零的列的数量？修正查询计数错误

我需要编写一个T-SQL查询，用来统计值大于零的列的数量，示例效果如下：

我尝试了下面的查询，但其中total字段的计数结果不对：

select 
   Site, cu_id, 
   count(distinct case when divi='EX_TO1' then inv_no end)EX_TO1, 
   count(distinct case when divi='EX_TO2' then inv_no end)EX_TO2, 
   count(distinct case when divi='EX_TO3' then inv_no end)EX_TO3, 
   count(distinct case when divi in ('EX_TO4','EX_TO9') then inv_no end)EX_TO4, 
   count(distinct case when divi='EX_TO5' then inv_no end)EX_TO5, 
   count(distinct case when divi='EX_TO6' then inv_no end)EX_TO6, 
   count(distinct case when divi='EX_TO7' then inv_no end)EX_TO7, 
   count(distinct case when divi='EX_TO8' then inv_no end)EX_TO8, 
   count(distinct inv_no)total 
from OSH 
group by Site,cu_id 
order by  Site,cu_id asc

问题原因 #

你当前的total用count(distinct inv_no)统计的是所有不同inv_no的总数，但这和你要的“值大于零的列的数量”完全不是一回事——你需要统计的是前面8个统计列（EX_TO1到EX_TO8）中数值大于0的列的个数，而非订单号的总数。

修正后的查询 #

把原查询作为子查询，在外层通过判断每个列是否大于0来求和，得到符合要求的total：

select 
    Site, cu_id,
    EX_TO1, EX_TO2, EX_TO3, EX_TO4, EX_TO5, EX_TO6, EX_TO7, EX_TO8,
    -- 逐个判断列值是否大于0，是则加1，最后求和得到总数
    (
        CASE WHEN EX_TO1 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO2 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO3 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO4 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO5 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO6 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO7 > 0 THEN 1 ELSE 0 END +
        CASE WHEN EX_TO8 > 0 THEN 1 ELSE 0 END
    ) as total
from (
    select 
        Site, cu_id, 
        count(distinct case when divi='EX_TO1' then inv_no end)EX_TO1, 
        count(distinct case when divi='EX_TO2' then inv_no end)EX_TO2, 
        count(distinct case when divi='EX_TO3' then inv_no end)EX_TO3, 
        count(distinct case when divi in ('EX_TO4','EX_TO9') then inv_no end)EX_TO4, 
        count(distinct case when divi='EX_TO5' then inv_no end)EX_TO5, 
        count(distinct case when divi='EX_TO6' then inv_no end)EX_TO6, 
        count(distinct case when divi='EX_TO7' then inv_no end)EX_TO7, 
        count(distinct case when divi='EX_TO8' then inv_no end)EX_TO8
    from OSH 
    group by Site,cu_id 
) as sub_query
order by Site, cu_id asc

说明 #

• 每个CASE语句会判断对应列的统计值是否大于0，满足条件则返回1，否则返回0
• 将这些1和0相加，得到的就是值大于0的列的数量
• 该写法兼容所有SQL Server版本，如果你使用的是SQL Server 2017及以上，也可以用IIF函数简化CASE语句，效果一致

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