CREATE TABLE report
( id NUMBER(2),
year NUMBER(4),
month NUMBER(2),
amt NUMBER(3)) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 1, 110 ) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 2, 120) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 3, 130) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 4, 140) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 5, 150) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 6, 160) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 7, 170) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 8, 180) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 9, 190) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 10, 100) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 11, 110) ;
INSERT INTO report( id, year, month, amt ) VALUES(1, 2011, 12, 110) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 1, 110 ) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 2, 120) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 3, 130) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 4, 140) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 5, 150) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 6, 160) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 7, 170) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 8, 180) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 9, 190) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 10, 100) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 11, 110) ;
INSERT INTO report( id, year, month, amt ) VALUES(2, 2012, 12, 110) ;
-- 逐級匯總
select id, year, month, sum(amt)
from report
group by rollup( id, year, month ) ;
ID YEAR MONTH SUM(AMT)
---------- ---------- ---------- ----------
1 2011 1 220
1 2011 2 240
1 2011 3 260
1 2011 4 280
1 2011 5 300
1 2011 6 320
1 2011 7 340
1 2011 8 360
1 2011 9 380
1 2011 10 200
1 2011 11 220
1 2011 12 220
1 2011 3340 -- 1, 2011, 小計
1 3340 -- 1, 小計
2 2012 1 110
2 2012 2 120
2 2012 3 130
2 2012 4 140
2 2012 5 150
2 2012 6 160
2 2012 7 170
2 2012 8 180
2 2012 9 190
2 2012 10 100
2 2012 11 110
2 2012 12 110
2 2012 1670 -- 2, 2012, 小計
2 1670 -- 2, 小計
5010 -- 1+2, 總計
如果只要每年的小計, SQL如下
select *
from (
select id, year, month, sum(amt)
from report
group by rollup( id, year, month ) )
where id is not null
and year is not null ;
ID YEAR MONTH SUM(AMT)
---------- ---------- ---------- ----------
1 2011 1 220
1 2011 2 240
1 2011 3 260
1 2011 4 280
1 2011 5 300
1 2011 6 320
1 2011 7 340
1 2011 8 360
1 2011 9 380
1 2011 10 200
1 2011 11 220
1 2011 12 220
1 2011 3340
2 2012 1 110
2 2012 2 120
2 2012 3 130
2 2012 4 140
2 2012 5 150
2 2012 6 160
2 2012 7 170
2 2012 8 180
2 2012 9 190
2 2012 10 100
2 2012 11 110
2 2012 12 110
2 2012 1670