有一个数据库表 acc je flag 10 a 20 a 50 b 10 b 现在想用一个语句 吧 flag = a 和 b 的分开合计.达到如下效果a b 30 60 我用了select a = case flag ='a' then sum(je) else 0, b = case flag ='b' then sum(je) else 0 from acc这样语法好像有问题,请教该如何写?
select a = sum(case flag ='a' then je else 0 end), b = sum(case flag ='b' then je else 0 end) from acc
select (case flag when 'a' then je else 0) AS b,(case flag when 'b' then je else 0) AS b from acc
傻,忘了sum了 select sum(case flag when 'a' then je else 0) AS b,sum(case flag when 'b' then je else 0) AS b from acc
select sum(case flag when 'a' then je else 0) AS a,sum(case flag when 'b' then je else 0) AS b from acc
select a = sum(case when flag ='a' then je else 0 end), b =sum(case when flag ='b' then je else 0 end) from acc
--建立测试环境 create table #acc(je int,flag varchar(10)) insert #acc(je,flag) select '10','a' union all select '20','a' union all select '50','b' union all select '10','b' go --执行测试语句 select a = sum(case when flag ='a' then je else 0 end), b = sum(case when flag ='b' then je else 0 end) from #acc go --删除测试环境 drop table #acc go /*--测试结果 a b ----------- ----------- 30 60(1 row(s) affected)*/
select a = case flag ='a' then sum(je) else 0, b = case flag ='b' then sum(je) else 0 from acc这样语法好像有问题,请教该如何写?------ select a=sum(case when flag='a' then je else 0 end), b=sum(case when flag='b' then je else 0 end) from acc
select a = sum(case when flag ='a' then je else 0 end), b = sum(case when flag ='b' then je else 0) from acc
少了个end select a = sum(case when flag ='a' then je else 0 end), b = sum(case when flag ='b' then je else 0 end) from acc
select sum(case flag when 'a' then je else 0 end) AS a,sum(case flag when 'b' then je else 0 end) AS b from acc
select a = case when flag ='a' then sum(je) else 0 end , b = case when flag ='b' then sum(je) else 0 end from acc
一句group by就搞定了,这么麻烦干嘛 select sum(je) as count from a group by flag
select a = sum(case flag ='a' then je else 0 end),
b = sum(case flag ='b' then je else 0 end)
from acc
from acc
select sum(case flag when 'a' then je else 0) AS b,sum(case flag when 'b' then je else 0) AS b
from acc
from acc
else 0 end),
b =sum(case when flag ='b' then je
else 0 end)
from acc
create table #acc(je int,flag varchar(10))
insert #acc(je,flag)
select '10','a' union all
select '20','a' union all
select '50','b' union all
select '10','b'
go
--执行测试语句
select a = sum(case when flag ='a' then je else 0 end),
b = sum(case when flag ='b' then je else 0 end)
from #acc
go
--删除测试环境
drop table #acc
go
/*--测试结果
a b
----------- -----------
30 60(1 row(s) affected)*/
else 0,
b = case flag ='b' then sum(je)
else 0
from acc这样语法好像有问题,请教该如何写?------
select a=sum(case when flag='a' then je else 0 end),
b=sum(case when flag='b' then je else 0 end)
from acc
else 0 end),
b = sum(case when flag ='b' then je
else 0)
from acc
else 0 end),
b = sum(case when flag ='b' then je
else 0 end)
from acc
from acc
else 0 end ,
b = case when flag ='b' then sum(je)
else 0 end
from acc
select sum(je) as count from a group by flag