建表SQL如下:CREATE TABLE test99 (
xm char(2),
year char(4),
je number(20,2));
insert into test99 (xm, year, je)
values ('03', '2006', '1253725.69');insert into test99 (xm, year, je)
values ('03', '2007', '1367158.69');insert into test99 (xm, year, je)
values ('03', '2008', '1381985.67');insert into test99 (xm, year, je)
values ('03', '2009', '1438089.2');insert into test99 (xm, year, je)
values ('03', '2010', '849691.47');insert into test99 (xm, year, je)
values ('04', '2006', '13216330.19');insert into test99 (xm, year, je)
values ('04', '2007', '25443586.93');insert into test99 (xm, year, je)
values ('04', '2008', '41946908.54');insert into test99 (xm, year, je)
values ('04', '2009', '29421885.81');insert into test99 (xm, year, je)
values ('04', '2010', '32167391.87');insert into test99 (xm, year, je)
values ('05', '2006', '1715527.08');insert into test99 (xm, year, je)
values ('05', '2007', '4772401.67');insert into test99 (xm, year, je)
values ('05', '2008', '948110.37');insert into test99 (xm, year, je)
values ('06', '2006', '15101411.4');insert into test99 (xm, year, je)
values ('06', '2007', '16158082.55');insert into test99 (xm, year, je)
values ('06', '2008', '8819427.64');insert into test99 (xm, year, je)
values ('06', '2009', '3305963.02');insert into test99 (xm, year, je)
values ('06', '2010', '433555.25');
==============================
期待查询结果如下:
2006 2007 2008 2009 20101253725.69 1367158.69 1381985.67 1438089.2 849691.47
13216330.19 25443586.93 41946908.54 29421885.81 32167391.87
1715527.08 4772401.67 948110.37
15101411.4 16158082.55 8819427.64 3305963.02 433555.25如何实现呢??
xm char(2),
year char(4),
je number(20,2));
insert into test99 (xm, year, je)
values ('03', '2006', '1253725.69');insert into test99 (xm, year, je)
values ('03', '2007', '1367158.69');insert into test99 (xm, year, je)
values ('03', '2008', '1381985.67');insert into test99 (xm, year, je)
values ('03', '2009', '1438089.2');insert into test99 (xm, year, je)
values ('03', '2010', '849691.47');insert into test99 (xm, year, je)
values ('04', '2006', '13216330.19');insert into test99 (xm, year, je)
values ('04', '2007', '25443586.93');insert into test99 (xm, year, je)
values ('04', '2008', '41946908.54');insert into test99 (xm, year, je)
values ('04', '2009', '29421885.81');insert into test99 (xm, year, je)
values ('04', '2010', '32167391.87');insert into test99 (xm, year, je)
values ('05', '2006', '1715527.08');insert into test99 (xm, year, je)
values ('05', '2007', '4772401.67');insert into test99 (xm, year, je)
values ('05', '2008', '948110.37');insert into test99 (xm, year, je)
values ('06', '2006', '15101411.4');insert into test99 (xm, year, je)
values ('06', '2007', '16158082.55');insert into test99 (xm, year, je)
values ('06', '2008', '8819427.64');insert into test99 (xm, year, je)
values ('06', '2009', '3305963.02');insert into test99 (xm, year, je)
values ('06', '2010', '433555.25');
==============================
期待查询结果如下:
2006 2007 2008 2009 20101253725.69 1367158.69 1381985.67 1438089.2 849691.47
13216330.19 25443586.93 41946908.54 29421885.81 32167391.87
1715527.08 4772401.67 948110.37
15101411.4 16158082.55 8819427.64 3305963.02 433555.25如何实现呢??
select xm,
max(decode(year,'2006',je)) "2006",
max(decode(year,'2007',je)) "2007",
max(decode(year,'2008',je)) "2008",
max(decode(year,'2009',je)) "2009",
max(decode(year,'2010',je)) "2010"
from test99
group xm;
sum(decode(year,'2007',je)) 2007,
sum(decode(year,'2008',je)) 2008,
sum(decode(year,'2009',je)) 2009,
sum(decode(year,'2010',je)) 2010
from test99
group by xm
sum(case year when '2006' then je else 0 end) 2006,
sum(case year when '2007' then je else 0 end) 2007,
sum(case year when '2008' then je else 0 end) 2008,
sum(case year when '2009' then je else 0 end) 2009
from test99
group by xm;