数据库里有个表:
ID Size Number
A X1 3
A X2 5
A X3 4
B X2 7问题:我要按ID分组,将不同的Size横向显示 如下:ID X1 X2 X3 Amount(X1+X2+X3)
A 3 5 4 12
B 7 7
ID Size Number
A X1 3
A X2 5
A X3 4
B X2 7问题:我要按ID分组,将不同的Size横向显示 如下:ID X1 X2 X3 Amount(X1+X2+X3)
A 3 5 4 12
B 7 7
select id ,
sum(case when size=x1 then number else 0) as x1,
sum(case when size=x1 then number else 0) as x2,
sum(case when size=x1 then number else 0) as x3,
sum(number) as Amount(X1+X2+X3)
from table
group by id
select a.*,(a.x1+a.x2+a.x3) Amount(x1+x2+x3)
(select id ,
sum(case size when x1 then number else 0 end) as x1,
sum(case size when x2 then number else 0 end) as x2,
sum(case size when x3 then number else 0 end) as x3,
from table a
group by id)
group by a.id