Oracle关于Group by合计、小计的使用

最新推荐文章于 2024-07-26 17:53:02 发布

ceclar123

最新推荐文章于 2024-07-26 17:53:02 发布

阅读量1.2w

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/ceclar123/article/details/9039637

Oracle 专栏收录该内容

28 篇文章

订阅专栏

方法：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 语法的

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

后面继续更新----