mysql select in 变量_SQL之leecote题（1）：变量列@i的使用——求部门工资前三高的所有员工...

前言：

本题主要讲解变量列 @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;

select * from department;

（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 ;

思路：首先定义(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运行结果如下

当然一般不会这么用，简单的从1开始递增就行

select (@i:=@i+1) as rownum, surname, personal_name from student, (select @i:=0) as init;

解释来源：https://blog.csdn.net/qq_27922171/article/details/86477544