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(
方法：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 语法的
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
后面继续更新----
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献3条内容
所有评论(0)