oracle 明细小计,Oracle关于Group by合计、小计的使用

方法：group by rollup(col1,col2)

group by cube(col1,col2)

具体例子：

select G_GOODS_OUT.DEPTID,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '01', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_01,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '02', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_02,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '03', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_03,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '04', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_04,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '05', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_05,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '06', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_06,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '07', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_07,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '08', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_08,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '09', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_09,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '10', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_10,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '11', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_11,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '12', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_12,

sum(G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE) YEAR_SUM

from G_GOODS_OUT

inner join G_GOODS ON G_GOODS.ID=G_GOODS_OUT.GOODS_ID

where 1=1 and (G_GOODS_OUT.ACTDATE>=to_date('2013-01-01 00:00:01','yyyy-MM-dd hh24:mi:ss') and G_GOODS_OUT.ACTDATE<=to_date('2013-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss'))

group by ROLLUP(G_GOODS_OUT.DEPTID)

统计结果：

部门ID

一月

二月

三月

四月

五月

六月

七月

八月

九月

十月

十一月

十二月

合计

1

1

2

3

4

5

6

7

8

9

10

11

12

78

1

2

3

4

5

6

7

8

9

10

11

12

78

例子2：

select G_GOODS_OUT.DEPTID,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '01', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_01,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '02', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_02,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '03', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_03,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '04', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_04,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '05', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_05,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '06', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_06,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '07', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_07,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '08', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_08,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '09', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_09,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '10', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_10,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '11', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_11,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '12', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_12,

sum(G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE) YEAR_SUM

from G_GOODS_OUT

inner join G_GOODS ON G_GOODS.ID=G_GOODS_OUT.GOODS_ID

where 1=1 and (G_GOODS_OUT.ACTDATE>=to_date('2013-01-01 00:00:01','yyyy-MM-dd hh24:mi:ss') and G_GOODS_OUT.ACTDATE<=to_date('2013-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss'))

group by CUBE(G_GOODS_OUT.DEPTID)

结果与上述的相似

例子：关于grouping的使用

select (case when grouping(G_GOODS_OUT.DEPTID)=1 then '合计' else cast(G_GOODS_OUT.DEPTID as varchar(20)) end ) DEPTID,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '01', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_01,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '02', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_02,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '03', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_03,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '04', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_04,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '05', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_05,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '06', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_06,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '07', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_07,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '08', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_08,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '09', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_09,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '10', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_10,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '11', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_11,

sum(decode(to_char(G_GOODS_OUT.ACTDATE,'MM'), '12', G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE,null)) as MONTH_12,

sum(G_GOODS_OUT.SQNUM*G_GOODS.U_PRICE) YEAR_SUM

from G_GOODS_OUT

inner join G_GOODS ON G_GOODS.ID=G_GOODS_OUT.GOODS_ID

where 1=1 and (G_GOODS_OUT.ACTDATE>=to_date('2013-01-01 00:00:01','yyyy-MM-dd hh24:mi:ss') and G_GOODS_OUT.ACTDATE<=to_date('2013-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss'))

group by CUBE(G_GOODS_OUT.DEPTID)

结果为：

部门ID

一月

二月

三月

四月

五月

六月

七月

八月

九月

十月

十一月

十二月

合计

1

1

2

3

4

5

6

7

8

9

10

11

12

78

合计

1

2

3

4

5

6

7

8

9

10

11

12

78 grouping(col1)的结果为1表示是合计结果，否则是普通聚合结果

注：其实sql Server也是支持group by rollup与group by cube 语法的

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

后面继续更新----