[Oracle]高效的SQL语句之分析函数(一)--sum()
实际应用中我们可以通过sum()统计出组中的总计或者是累加值,具体示例如下:
1.创建演示表
create table
emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key (deptno);
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key (deptno);
2. sum()语句如下:
select
deptno,
ename,
sal,
--按照部门薪水累加(order by改变了分析函数的作用,只工作在当前行和前一行,而不是所有行)
sum(sal) over (partition by deptno order by sal) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept, --统计一个部门的薪水
sum(sal) over (order by deptno,sal) CumTot, --所有雇员的薪水一行一行的累加
sum(sal) over () TotSal --统计总薪水
from emp
order by deptno, sal
ename,
sal,
--按照部门薪水累加(order by改变了分析函数的作用,只工作在当前行和前一行,而不是所有行)
sum(sal) over (partition by deptno order by sal) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept, --统计一个部门的薪水
sum(sal) over (order by deptno,sal) CumTot, --所有雇员的薪水一行一行的累加
sum(sal) over () TotSal --统计总薪水
from emp
order by deptno, sal
3. 结果如下:
10 MILLER 1300.00 1300 8750 1300 29025
10 CLARK 2450.00 3750 8750 3750 29025
10 KING 5000.00 8750 8750 8750 29025
20 SMITH 800.00 800 10875 9550 29025
20 ADAMS 1100.00 1900 10875 10650 29025
20 JONES 2975.00 4875 10875 13625 29025
20 SCOTT 3000.00 10875 10875 19625 29025
20 FORD 3000.00 10875 10875 19625 29025
30 JAMES 950.00 950 9400 20575 29025
30 WARD 1250.00 3450 9400 23075 29025
30 MARTIN 1250.00 3450 9400 23075 29025
30 TURNER 1500.00 4950 9400 24575 29025
30 ALLEN 1600.00 6550 9400 26175 29025
30 BLAKE 2850.00 9400 9400 29025 29025
10 CLARK 2450.00 3750 8750 3750 29025
10 KING 5000.00 8750 8750 8750 29025
20 SMITH 800.00 800 10875 9550 29025
20 ADAMS 1100.00 1900 10875 10650 29025
20 JONES 2975.00 4875 10875 13625 29025
20 SCOTT 3000.00 10875 10875 19625 29025
20 FORD 3000.00 10875 10875 19625 29025
30 JAMES 950.00 950 9400 20575 29025
30 WARD 1250.00 3450 9400 23075 29025
30 MARTIN 1250.00 3450 9400 23075 29025
30 TURNER 1500.00 4950 9400 24575 29025
30 ALLEN 1600.00 6550 9400 26175 29025
30 BLAKE 2850.00 9400 9400 29025 29025
[Oracle]高效的SQL语句之分析函数(二)--max()
如果我们按照示例想得到每个部门薪水值最高的雇员的纪录,可以有四种方法实现:
先创建示例表
create table
emp
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
as
select * from scott.emp;
alter table emp
add constraint emp_pk
primary key (empno);
create table dept
as
select * from scott.dept;
alter table dept
add constraint dept_pk
primary key(deptno);
方法1.emp中的每一行都会进行max比较,费时
select * from emp emp1 where emp1.sal=(select max(emp2.sal) from emp emp2 where emp2.deptno=emp1.deptno)
方法2.先子查询查找出max sal,然后与emp表相关联,如果逻辑复杂会产生较多代码
select * from emp emp1,(select deptno,max(sal) maxsal from emp emp2 group by emp2.deptno) emp3 where emp1.deptno=emp3.deptno and emp1.sal=emp3.maxsal
方法3.使用max分析函数
select deptno,maxsal,empno from
(
select max(sal) over (partition by deptno) maxsal,emp.* from emp) emp2
where emp2.sal=emp2.maxsal
select max(sal) over (partition by deptno) maxsal,emp.* from emp) emp2
where emp2.sal=emp2.maxsal
方法4.使用dense_rank分析函数,如果一个部门可能存在多笔最大薪水,就不能使用row_number()分析函数
select deptno,sal,empno from
(
select emp.*,DENSE_RANK() over (partition by deptno order by sal desc) rownumber from emp) emp2
where rownumber=1
select emp.*,DENSE_RANK() over (partition by deptno order by sal desc) rownumber from emp) emp2
where rownumber=1
结果如下:
10 5000.00 7839
20 3000.00 7788
20 3000.00 7902
30 2850.00 7698
20 3000.00 7788
20 3000.00 7902
30 2850.00 7698
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219982/viewspace-492459/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/219982/viewspace-492459/