create table emp(
deptno varchar2(20) ,
empno varchar2(20),
ename varchar(20),
sal number
);
–显示各部门员工的工资,并附带显示该部分的最高工资。
select e.deptno,
e.empno,
e.ename,
e.sal,
last_value(e.sal)
over(partition by e.deptno
order by e.sal rows
--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
--unbounded:不受控制的,无限的
--preceding:在...之前
--following:在...之后
between unbounded preceding and unbounded following) max_sal
from 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_sal
from emp
–对各部门进行分组,并附带显示第一行至当前行的汇总
select
empno,
ename,
deptno,
sal,
sum(sal) over(partition by deptno order by empno
) max_sal
from
emp;
–当前行至最后一行的汇总
select empno,
ename,
deptno,
sal,
--注意rows between current row and unbounded following 指当前行到最后一行的汇总
sum(sal) over(partition by deptno
order by empno
rows between current row and unbounded following) sum_sal
from emp;
–当前行的上一行(rownum-1)到当前行的汇总
select empno,
ename,
deptno,
sal,
--注意rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总
sum(sal) over(partition by deptno
order by ename rows
between 1 preceding and current row) sum_sal
from 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 deptno
order by ename
rows between 1 preceding and 2 following) sum_sal
from emp;
–练习取最后一个值
select
deptno,
empno,
ename,
sal,
last_value(sal) over(partition by deptno order by sal desc rows between unbounded preceding and unbounded following) sum_sal
from
emp;
–练习使用first_value()
select deptno,
empno,
ename,
sal,
first_value(sal)
over(partition by deptno
order by sal
rows between unbounded preceding and unbounded following) max_sal
from emp;
本文地址:https://blog.csdn.net/JISHI412/article/details/107395566
如对本文有疑问, 点击进行留言回复!!
金蝶KIS商贸版 专业版 账套管理查看账套没有用户名 登录账套用户不存在 解决方法.
QueryWarpper的使用方法,MyBatics Plus的查询方法处理
sqlServer数据库表无法查询,无法删除。“表发生死锁”。“已超过了锁请求超时时段。”
网友评论