SQL之leecote题（1）：变量列@i的使用——求部门工资前

2019-11-13 本文已影响0人 Gaafung峰

前言：

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