MYSQL 寒假自学 2022 十二 （十二）

-- 第几个值
select 
 dname,
 ename,
 salary,
 hiredate,
 nth_value(salary,2) over(partition by dname order by hiredate)as second_salary,
 nth_value(salary,3) over(partition by dname order by hiredate )as third_salary
from employee;

-- 分等级

select 
 dname,
 ename,
 salary,
 hiredate,
 ntile(3) over(partition by dname order by hiredate )as nt
from employee;#除得尽

select 
 dname,
 ename,
 salary,
 hiredate,
 ntile(4) over(partition by dname order by hiredate )as nt
from employee;#除不尽


select
*
from
(
select 
 dname,
 ename,
 salary,
 hiredate,
 ntile(3) over(partition by dname order by hiredate )as nt
from employee
) t
where t.nt=1;