Oracle关于Group by合计、小计的使用
来源:互联网 发布:启航营销软件 编辑:程序博客网 时间:2024/05/12 04:18
方法: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)
统计结果:
例子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)
结果为:
注:其实sql Server也是支持group by rollup与group by cube 语法的
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
后面继续更新----
- Oracle关于Group by合计、小计的使用
- oracle group by rollup实现小计、合计
- oracle 小计合计之 group by grouping sets
- oracle 小计,合计的算法
- Oracle 使用分析函数实现小计合计
- ------------------合计、小计的用法-----------------
- 报表的小计、合计
- Oracle分组扩展函数的使用(主要增加小计及合计金额)
- ORACLE中每个分组后加个合计,最后总合计的GROUP BY rollup,和中文排序
- Oracle Group By 用法之 —— Rollup (小计功能)
- Oracle中group by 的使用
- 关于group by子句使用的注意事项
- hibernate 关于group by的使用问题
- oracle的group by
- oracle数据库--关于使用分组函数以及group by的注意点
- EXTJS. Grid总计+oracle小计合计
- 小计 合计
- 合计小计
- SSIS Foreach 如何限定两种文件扩展名?
- IOS 的loadView 及使用loadView中初始化View注意的问题。(死循环并不可怕)
- Linux中常用的系统监控命令简介(ps , vmstat ,uptime,free,iostat)
- MFC多线程的进度条设计
- 【OpenCV】在没有安装OpenCV的电脑运行OpenCV程序
- Oracle关于Group by合计、小计的使用
- C#窗体应用程序(窗体传值)MDI
- 徒手打造一款PK 名片全能王 的名片识别应用
- 在 Visual C++ 中以错误的顺序链接 CRT 库和 MFC 库时出现 LNK2005 错误
- 深入理解C++中的mutable关键字
- iOS开发问题小结
- socket阻塞与非阻塞、同步与异步,五种I/O模型总结
- mark
- 【OpenCV】SIFT原理与源码分析