前言：
本题主要讲解变量列 @i 和 order by 的结合使用。
利用mysql的@i变量进行排序，结合 order by 使用 以达到 对员工工资进行排序筛选的目的。
题目：
Employee 表包含所有员工信息，每个员工有其对应的工号Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释：
IT 部门中，Max 获得了最高的工资，Randy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门（Sales）只有两名员工，Henry 的工资最高，Sam 的工资排第二。
解题步骤：
（1）插入数据：
#进入数据库
use leecote;
#创建employee表
create table employee (
id int(20),
name varchar(20),
salary int(20),
departmentid int(20)
);
#在employee表插入数据
insert into employee values
(1,"Joe",85000,1),
(2,"Henry",80000,2),
(3,"Sam",60000,2),
(4,"Max",90000,1),
(5,"Janet",69000,1),
(6,"Randy",85000,1),
(7,"Will",70000,1);
#插入额外两行验证数据准确性
insert into employee values
(7,"sll",70000,1);
insert into employee values
(7,"sdll",90000,1);
#创建department表
create table department (
id int(20),
name varchar(20)
);
#在department表插入数据
insert into department values (1,"IT"),(2,"Sales");
select * from employee;
image.png
select * from department;
image.png
（2）解法：
select d.name as department,e.name as employee,salary from employee e join department d on e.departmentid = d.id
where (departmentid,salary) in (select departmentid,salary from
(
select *,@i:=if(@did=departmentid,if(@sa=salary,@i,@i+1),1) as rank,@sa:=salary,
@did:=departmentid from employee e,(select @i:=0,@sa:=-1,@did:=null) t
order by departmentid,salary desc) a where rank <= 3) order by department, salary desc ;
image.png
思路：首先定义 (select @i:=0,@sa:=-1,@did:=null) ，
利用if实现判断，达到变量自增的效果
@i:=if(@did=departmentid,if(@sa=salary,@i,@i+1),1) as rank,@sa:=salary,
@did:=departmentid
（3）如何理解@i：
Oracle中有一个伪列rownum，可以在生成查询结果表的时候生成一组递增的序列号。MySQL中没有这个伪列，但是有时候要用，可以用如下方法模拟生成一列自增序号。
（1）sql示例:select (@i:=@i+5) as rownum, surname, personal_name from student, (select @i:=100) as init;
解释: 上述sql中，红色值为自定义的初始序号，蓝色值为递增规则，上述sql运行结果如下
image.png
当然一般不会这么用，简单的从1开始递增就行
select (@i:=@i+1) as rownum, surname, personal_name from student, (select @i:=0) as init;
image.png
解释来源：https://blog.csdn.net/qq_27922171/article/details/86477544
网友评论