nt (YEAR, MONTH, AMOUNT) values ('1991', '2', 1.20); insert into amount (YEAR, MONTH, AMOUNT) values ('1991', '3', 1.30); insert into amount (YEAR, MONTH, AMOUNT) values ('1991', '4', 1.40); insert into amount (YEAR, MONTH, AMOUNT) values ('1992', '1', 2.10); insert into amount (YEAR, MONTH, AMOUNT) values ('1992', '2', 2.20); insert into amount (YEAR, MONTH, AMOUNT) values ('1992', '3', 2.30); insert into amount (YEAR, MONTH, AMOUNT) values ('1992', '4', 2.40);
思路:group by year统计年，再条件判断每个月，统计所在月，所在月的合计值显示出来
select year,
(select sum(amount)
from amount m
where month = 1
and m.year = amount.year) as m1,
(select sum(amount)
from amount m
where month = 2
and m.year = amount.year) as m2,
(select sum(amount)
from amount m
where month = 3
and m.year = amount.year) as m3,
(select sum(amount)
from amount m
where month = 4
and m.year = amount.year) as m4
from amount
group by year;
SQL>
select year,
2 (select sum(amount)
3 from amount m
4 where month = 1
5 and m.year = amount.year) as m1,
6 (select sum(amount)
7 from amount m
8 where month = 2
9 and m.year = amount.year) as m2,
10 (select sum(amount)
11 from amount m
12 where month = 3
13 and m.year = amount.year) as m3,
14 (select sum(amount)
15 from amount m
16 where month = 4
17 and m.year = amount.year) as m4
18 from amount
19 group by year;
YEAR M1 M2 M3 M4
---- ---------- ---------- ---------- ----------
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4