select sum(case when a>0 then 1 else 0 end ) as a, sum(case when b>0 then 1 else 0 end ) as b, sum(case when c>0 then 1 else 0 end ) as c, sum(case when d>0 then 1 else 0 end ) as d from tb
SELECT sum(case when A>0 then 1 else 0 end ) as a ,sum(case when B>0 then 1 else 0 end ) as b ,sum(case when C>0 then 1 else 0 end ) as c ,sum(case when D>0 then 1 else 0 end ) as d FROM [TB]
select sum(case when a > 0 then 1 else 0 end) A, sum(case when b > 0 then 1 else 0 end) B, sum(case when c > 0 then 1 else 0 end) C, sum(case when d > 0 then 1 else 0 end) D from tb
declare @表 table (A int,B int,C int,D int) insert into @表 select 2,1,3,null union all select 1,0,0,3 union all select 4,2,2,0select A=sum(sign(A)), B=sum(sign(B)), C=sum(sign(C)), D=sum(sign(D)) from @表 /* A B C D ----------- ----------- ----------- ----------- 3 2 2 1 */如果没有负数的话,这样比较简单。
sum(case when a>0 then 1 else 0 end ) as a,
sum(case when b>0 then 1 else 0 end ) as b,
sum(case when c>0 then 1 else 0 end ) as c,
sum(case when d>0 then 1 else 0 end ) as d
from tb
,sum(case when B>0 then 1 else 0 end ) as b
,sum(case when C>0 then 1 else 0 end ) as c
,sum(case when D>0 then 1 else 0 end ) as d
FROM [TB]
sum(case when a > 0 then 1 else 0 end) A,
sum(case when b > 0 then 1 else 0 end) B,
sum(case when c > 0 then 1 else 0 end) C,
sum(case when d > 0 then 1 else 0 end) D
from tb
declare @表 table (A int,B int,C int,D int)
insert into @表
select 2,1,3,null union all
select 1,0,0,3 union all
select 4,2,2,0select
A=sum(sign(A)),
B=sum(sign(B)),
C=sum(sign(C)),
D=sum(sign(D)) from @表
/*
A B C D
----------- ----------- ----------- -----------
3 2 2 1
*/如果没有负数的话,这样比较简单。