MYSQL 寒假自学 2022 十二 （七）

use mydb4;
create table employee
(
	dname varchar(20),	#部门名
	eid varchar(20),
	ename varchar(20),
	hiredate date,	#入职日期
	salary double #薪资
);
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);

insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

select
	dname,
	ename,
	salary,
	row_number() over(partition by dname order by salary desc) as rn1,
	rank() over(partition by dname order by salary desc) as rn2,
	dense_rank() over(partition by dname order by salary desc) as rn3
from employee;

-- 求出每个部门薪资排在前三名的员工-分组求topn
select 
*
from
(select
	dname,
	ename,
	salary,
	dense_rank() over(partition by dname order by salary desc) as rn
from employee
) t
where t.rn<=3;

-- 对所有员工进行全局排序（不分组）
select
	dname,
	ename,
	salary,
	dense_rank() over(order by salary desc) as rn
from employee;

序号函数