MYSQL 寒假自学 2022 十二 （ 八）

use mydb4;
select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate) as pv1
from employee;


select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname ) as pv1
from employee;# 将order by 删去，全部求和

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate) as pv1
from employee;

-- 控制相加的范围
select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between unbounded preceding and current row) as pv1
from employee; #从分组后的开始行加到当前行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and current row) as pv1
from employee; #从分组后,当前行的前三行一直加到当前行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and 1 following) as pv1
from employee; #从分组后,当前行的前三行一直加到当前行的后一行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between current row and  unbounded following) as pv1
from employee; #从当前行一直加到最后一行