【Oracle】rollup函数
2024-08-31 04:37:20
当我们在做报表统计的时候,很多时候需要用到‘合计’这个功能,比如我们想得到如下格式的报表:
这张表是按照deptno分组,然后按照deptno分组合计。rollup函数可以完美的解决这个问题。
1.建立一张t表:
create table T ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) );
2.插入数据:
insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 1500.00, 0.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 1600.00, 300.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 1250.00, 500.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 2975.00, 100.00, 20); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7654, 'MARTIN', 'SALESMAN', 1250.00, 1400.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 2850.00, 100.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 2450.00, 100.00, 10); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7788, 'SCOTT', 'ANALYST', 3000.00, 100.00, 20); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7839, 'KING', 'PRESIDENT', 5000.00, 100.00, 10); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 1100.00, 100.00, 20); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 950.00, 100.00, 30); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 3000.00, 100.00, 20); insert into t (EMPNO, ENAME, JOB, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 1300.00, 100.00, 10); commit;
3.使用rollup函数
select empno, ename, sum(sal) as sal, sum(comm) as comm, deptno from t group by rollup(deptno, (empno, ename)) having grouping(deptno) = 0;
The End!
最新文章
- VC调试闪退解决办法
- BZOJ3759: Hungergame 博弈论+线性基
- A Beginner's Guide To Understanding Convolutional Neural Networks(转)
- Asp.Net Web API 2第七课——Web API异常处理
- wordpress 中禁止更新提示
- 数据源加密-JDBC调用方式加密示例
- unity3d银联支付出现闪退
- jquery中的选择器01
- Managed Switch: Confs
- Excel 按模板格式导出
- ORACLE环境变量设置
- Linux内核开发之将驱动程序添加到内核
- 在nuget上发布自己的程序集教程
- 读 Spring实战 遇到的问题记录(一)
- [Swift]LeetCode491. 递增子序列 | Increasing Subsequences
- vue 移动端轻量日期组件不依赖第三方库
- gravity 和 layout_gravity
- Springzz中使用监听器,用于容器一启动就加载准备数据(application范围内的数据,用于减轻服务器压力,不用每次都去查数据)
- 【C语言】两个指针(地址)相减
- 软工网络15团队作业4——Alpha阶段敏捷冲刺8.0