有一个表,表结构如下:
a varchar(20);
b varchar(20);
c numeric(9);
d numeric(9);
数据例如
a b c dA f 3.50 6.90
B f 8.33 5.64
C d 13.20 15.63根据b字段汇总输出c-d大于0的记录数,c-d小于0的记录数,以及c不等于d的记录数,语句怎么写,谢谢!
a varchar(20);
b varchar(20);
c numeric(9);
d numeric(9);
数据例如
a b c dA f 3.50 6.90
B f 8.33 5.64
C d 13.20 15.63根据b字段汇总输出c-d大于0的记录数,c-d小于0的记录数,以及c不等于d的记录数,语句怎么写,谢谢!
insert into @t select 'A','f',3.50,6.90
union all select 'B','f',8.33,5.64
union all select 'C','d',13.20,15.63select [c-d>0]=sum(case when c-d>0 then 1 else 0 end),
[c-d<0]=sum(case when c-d<0 then 1 else 0 end),
[c<>d]=sum(case when c<>d then 1 else 0 end)
from @t
insert into @t select 'A','f',3.50,6.90
union all select 'B','f',8.33,5.64
union all select 'C','d',13.20,15.63select b,
[c-d>0]=sum(case when c-d>0 then 1 else 0 end),
[c-d<0]=sum(case when c-d<0 then 1 else 0 end),
[c<>d]=sum(case when c<>d then 1 else 0 end)
from @t
group by b--少看了个条件,根据b字段