表字段:tch,bs,je(提出行,标识,金额)均为varchar类型,je需要sum!也就是说要类型转化下.
需求:按照tch和bs进行汇总.汇总出来的是
tch,jje,dje(标识为1和2,即jje,dje)我写的sql用了2个子查询,看着实在不爽啊....
写了两种方法都没能避免子查询..汗啊...
需求:按照tch和bs进行汇总.汇总出来的是
tch,jje,dje(标识为1和2,即jje,dje)我写的sql用了2个子查询,看着实在不爽啊....
写了两种方法都没能避免子查询..汗啊...
数据自己造,神马临时表...就一张表...
tch je bs
-------------------
888 1500 2
888 3000000 1
001 20300 1
001 11100 1
888 100000 2
888 3000100 1
001 100000 1
001 201200 2
select
tch,
bs,
je = SUM(cast(je as decimal(10, 2)))
from tb
where ISNUMERIC(je)=1
group by tch, bs
select tch,
sum(case when bs='1' then cast(je as money) else 0 end) as jje,
sum(case when bs='2' then cast(je as money) else 0 end) as dje
from T
group by tch
order by tch
insert into tb select '888','1500','2'
insert into tb select '888','3000000','1'
insert into tb select '001','20300','1'
insert into tb select '001','11100','1'
insert into tb select '888','100000','2'
insert into tb select '888','3000100','1'
insert into tb select '001','100000','1'
insert into tb select '001','201200','2'
go
select tch,SUM(case when bs='1' then je else 0 end) as jje,
SUM(case when bs='2' then je else 0 end) as dje
from tb
group by tch
/*
tch jje dje
---------- ----------- -----------
001 131400 201200
888 6000100 101500(2 行受影响)*/
go
drop table tb