Question

求SQL语句，参考图片内容，急………………

四楼的有个问题请教！
select id,sum(one),sum(two),sum(three)
from (select id,sum(consume) as one,0,0 from s where month = 1 group by id
union all select id,0,sum(consume) as two,0 from s where month = 2 group by id
union all select id,0,0,sum(consume) as three from s where month = 3 group by id)
group by id;
ORA-00904: "THREE": 标识符无效，这个执行有个错误，请大侠指点！

Answer 1

select 账号,sum(case when 月份= 1 then 消费else 0 end) 一月,sum(case when 月份= 2 then 消费else 0 end) 二月,sum(case when 月份= 3 then 消费 else 0 end) 三月 from 表 group by 账号;

Answer 2

select 账号,sum(1月)，sum(2月)，sum(3月)
from (
select 账号,sum(消费) as 1月,0,0
from 表 where 月份 = '1'
group by 账号
union all
select 账号,0,sum(消费) as 2月,0
from 表 where 月份 = '2'
group by 账号
union all
select 账号,0,0,sum(消费) as 3月
from 表 where 月份 = '3'
group by 账号
)
group by 账号

Answer 3

declare @sql nvarchar(1000)
set @sql = 'select 账号'
select @sql =@sql+ ', sum(case when 月份='''+rtrim(月份)+''' THEN 消费 ELSE 0 END) AS '''+月份+'''' from tablename group by 月份
set @sql = @sql + 'from tablename group by 账号'

Answer 4

select
帐号,
decode(月份,1,消费,0) '1月',
decode(月份,2,消费,0) '2月',
decode(月份,3,消费,0) '3月'
from table

追问

你这个有点问题
select id,
decode(month ,1,consume,0) one,
decode(month ,2,consume,0) two,
decode(month ,3,consume,0) three
from s
group by id;
这样就可以了

追答

哈哈, 回复太快了, 没看清小图