11 部门工资前三高的所有员工

[astak16]

题目

找出每个部门获得前三高工资的所有员工

create table employee (
	id int primary key auto_increment,
	name varchar(255),
	salary int,
	departmentId int
);
create table department (
	id int primary key auto_increment,
	name varchar(255)
);

insert into employee (name, salary, departmentId) values
('joe', 85000, 1),
('henry', 80000, 2),
('sam', 60000, 2),
('max', 90000, 1),
('janet', 69000, 1),
('randy', 85000, 1),
('will', 70000, 1);

insert into department(name) values('it'),('sales');

SQL

select department.id, employee.name, employee.salary from (
	select te.departmentId, te.salary,
		case ①
			when @pre=departmentId then @rank:=@rank + 1
			when @pre:=departmentId then @rank:=1
		end as 排名
	from (select @pre:=null, @rank:=0) tt,
	(
		select departmentId, salary from employee
		group by departmentId, salary
		order by departmentId, salary desc
	) te
) t
inner join department on t.departmentId = department.id
inner join employee on t.departmentId = employee.departmentId
and employee.salary = t.salary and 排名 <= 3
order by t.departmentId, t.salary desc;

解析

• 将 employee 按照 departmentId 和 salary 进行分组，将这个临时表命名为 te
• 使用 case ... when ... then ... end 和变量根据薪水算出排名，将这个临时表命名为 t。
• 使用两次 inner join 分别连接 department 和 employee
  • t 表和 department 表连接条件是 t.departmentId = department.id
  • t 表和 employee 表连接条件是 t.departmentId = employee.departmentId and t.salary = employee.salary ②

Tips：

① case 语句中 when 应该是条件，这里为什么用赋值 :=

1. 查询第一条数据进入 case 时， when @prev = departmentId then ... 执行的时，此时 @prev 为 null 不满足条件，所以它就会执行 when @prev := departmentId then ... ，此时 @prev 为第一条数据的 departmentId 由于赋值语句肯定为 true ，所以 @rank 就为 1
2. 查询第二条数据进入 case 时， when @prev = departmentId then ... 由于 @prev 有值了，下面的 when 就不会执行了。
3. 查询第三条数据进入 case 时， when @prev = departmentId then ... ，第三条数据的 departmentId 是一个新的值，此时不满足 @prev = departmentId ，就会进入第二个 when @prev := departmentId then ... 将 departmentId 的最新值赋值给 @prev
4. 按照上面步骤直到所有的数据都查询完。

② 这里为什么用 t.salary = employee.salary 而不用 t.name = employee.name

t.salary = employee.salary 作用是确定是同一个用，这里就有个问题，确定同一个人的话，为什么不用 name 做条件呢？ 这里是因为 te 按照 salary 和 department 进行分组，不考虑 name 的原因是可能会有两个人的 salary 是一样的，如果在加上 name 的话，就会出现两个 salary 相同的人，排名不一样。

现在给的数据有两个人的 salary 是一样的，可以将其中一个 salary 修改一下，就可以知道结果了。