求一SQL题目解法
现在有三个字段。一个是ID，一个字段是flag,一个是number
我想实现这样的SQL题目怎么求。
select sum(number),sum(number where flag=1),sum(number where flag =2) group by id
这样写MS SQL语法通不过。但我想达到这样的效果。求高手解决问题。
[解决办法]
select sum(number),
sum(case when flag=1 then number else 0 end),
sum(case when flag=2 then number else 0 end)
group by id
[解决办法]
select
sum(number),
sum(case when flag=1 then number else 0 end) as [Flag=1的合计],
sum(case when flag=2 then number else 0 end) as [Flag=2的合计]
group by id
[解决办法]
--我认为应该把ID加上.
select id,
sum(number) [sum],
sum(case flag when 1 then number end) [sum1],
sum(case flag when 2 then number end) [sum2]
from tb
group by id
--如果不加ID,后面的group by id没用.应该这么写:
select [sum] = (select sum(number) from tb),
sum1 = (select sum(number) from tb where flag = 1),
sum2 = (select sum(number) from tb where flag = 2)
[解决办法]
Select sum(number),sum(case when flag=1 then number else 0 end),sum(case when flag=2 then number else 0 end ) group by id
