一个表A:三个字段(Year, Quarter, Amount)
记录如下:2007 1 100
2007 2 110
2007 3 120
2007 4 130
2008 1 200
2008 2 210
2008 3 220
2008 4 230写一条SQL(最好使用SQL语句,不依赖于系统,考虑效率),查询结果如下:Year Quarter1 Quarter2 Quarter3 Quarter4
2007 100 110 120 130
2008 200 210 220 230
记录如下:2007 1 100
2007 2 110
2007 3 120
2007 4 130
2008 1 200
2008 2 210
2008 3 220
2008 4 230写一条SQL(最好使用SQL语句,不依赖于系统,考虑效率),查询结果如下:Year Quarter1 Quarter2 Quarter3 Quarter4
2007 100 110 120 130
2008 200 210 220 230
from
(select year,quarter quarter1,amount from a where quarter = 1)t1,
(select year,quarter quarter2,amount from a where quarter = 2)t2,
(select year,quarter quarter3,amount from a where quarter = 3)t3,
(select year,quarter quarter4,amount from a where quarter = 4)t4
where t1.year = t2.year
and t1.year = t3.year
and t1.year = t4.year
select t1.year,t1.quarter1,t2.quarter2,t3.quarter3,t4.quarter4
from
(select year,amount quarter1 from a where quarter = 1)t1,
(select year,amount quarter2 from a where quarter = 2)t2,
(select year,amount quarter3 from a where quarter = 3)t3,
(select year,amount quarter4 from a where quarter = 4)t4
where t1.year = t2.year
and t1.year = t3.year
and t1.year = t4.year;
insert into test values(2007,1,100);
insert into test values(2007,2,110);
insert into test values(2007,3,120);
insert into test values(2007,4,130);
insert into test values(2008,1,200);
insert into test values(2008,2,210);
insert into test values(2008,3,220);
insert into test values(2008,4,230);
commit;
select * from test t;
select t.year,
sum(decode(t.quarter, 1, t.amount)) Quarter1,
sum(decode(t.quarter, 2, t.amount)) Quarter2,
sum(decode(t.quarter, 3, t.amount)) Quarter3,
sum(decode(t.quarter, 4, t.amount)) Quarter4
from test t
group by t.year;
sum(case when a.quarter=1 then a.amount else 0 end) quarter1,
sum(case when a.quarter=2 then a.amount else 0 end) quarter2,
sum(case when a.quarter=3 then a.amount else 0 end) quarter3,
sum(case when a.quarter=4 then a.amount else 0 end) quarter4
from test a group by a.year;