oracle 小计，合计的算法

 ----

INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2007,'河北','石家庄',10)
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2007,'河北','承德',13);
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2007,'陕西','西安',23);
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2007,'陕西','石家庄',23);

INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2008,'河北','石家庄',35);
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2008,'河北','承德',31);
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2008,'陕西','西安',45);
INSERT INTO T (YEAR, PROVINCE,CITY ,sales)
VALUES(2008,'陕西','宝鸡',25);

 

select b.year,
       b.province,
       b.city,
       b.sales,
       sum(b.sales) over(partition by b.year, b.province) 小计,
       sum(b.sales) over(partition by b.year) 总合
  from T b
 
select nvl(Year,300),
      nvl(Province,'小计'),
      nvl(City,'小计'),
      sum(Sales)
from T
group by rollup( Year, Province, City )

 

-------------------------------

select case when year is null then '总计'
when province is null then '合计' when city is null then '小计'  else to_char(year) end,
decode(city,null,'',province),city,total
from (
   select year,
          province,
           city,sum(sales) total from t
   group by rollup(year,province,city)
 )

-----显示结果------------------------------------

1 2007 河北 承德 20
2 2007 河北 石家庄 10
3 小计   30
4 2007 陕西 石家庄 10
5 2007 陕西 西安 30
6 小计   40
7 合计   70
8 2008 河北 承德 23
9 2008 河北 石家庄 34
10 小计   57
11 2008 陕西 宝鸡 56
12 2008 陕西 西安 24
13 小计   80
14 合计   137
15 总计   207