Oracle 分析函数练习实例

article/2025/9/18 7:11:28

创建表

create table emp(deptno varchar2(20) ,empno varchar2(20),ename varchar(20),sal number
);

sql演示

–显示各部门员工的工资，并附带显示该部分的最高工资。

select e.deptno,e.empno,e.ename,e.sal,last_value(e.sal)over(partition by e.deptnoorder by e.sal rows --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录--unbounded：不受控制的，无限的--preceding：在...之前--following：在...之后between unbounded preceding and unbounded following) max_salfrom emp e;

在这里插入图片描述
–按照deptno分组，然后计算每组值的总和

select empno,ename,deptno,sal,sum(sal) over(partition by deptno order by ename rows between unbounded preceding and unbounded following) max_salfrom emp

在这里插入图片描述
–对各部门进行分组，并附带显示第一行至当前行的汇总

selectempno,ename,deptno,sal,sum(sal) over(partition by deptno order by empno) max_sal
fromemp;

在这里插入图片描述
–当前行至最后一行的汇总

select empno,ename,deptno,sal,--注意rows between current row and unbounded following 指当前行到最后一行的汇总sum(sal) over(partition by deptnoorder by empnorows between current row and unbounded following) sum_salfrom emp;

在这里插入图片描述
–当前行的上一行(rownum-1)到当前行的汇总

select empno,ename,deptno,sal,--注意rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总sum(sal) over(partition by deptnoorder by ename rows between 1 preceding and current row) sum_salfrom emp;

在这里插入图片描述
–当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总

select empno,ename,deptno,sal,--注意rows between 1 preceding and 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总sum(sal) over(partition by deptnoorder by enamerows between 1 preceding and 2 following) sum_salfrom emp;

在这里插入图片描述
–练习取最后一个值

selectdeptno,empno,ename,sal,last_value(sal) over(partition by deptno order by sal desc rows between unbounded preceding and unbounded following) sum_sal
fromemp;

在这里插入图片描述
–练习使用first_value()

select deptno,empno,ename,sal,first_value(sal)over(partition by deptnoorder by salrows between unbounded preceding and unbounded following) max_salfrom emp;