PS，1880后程序员

看不完的牙，写不完的程序，跑不完的步。

随笔 - 97, 文章 - 34, 评论 - 10, 引用 - 0

数据加载中……

Oracle 逐级汇总

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 ;