MYSQL基础上机练习题（六）排序问题综合应用

绝地灬酷狼 2022-07-16 15:18 258阅读 0赞

### 一、实验目的： ###

各种排序问题的综合应用

### 二、实验内容： ###

对MYSQL基础上机练习题（二）中所输入的数据进行

#### 1.排名：不重复排位排名、同值同位不占位排名、同值同位占位排名 ####

#### 2.组内前N名 ####

### 三、题目： ###

#### 1.对全部员工根据薪酬水平进行排序 ####

##### （1）薪酬水平相同时，按照姓名排序 #####

##### （2）薪酬水平相同时，排位相同，但不占位排序 #####

##### （3）薪酬水平相同时，排位相同且占位排序 #####

#### 2.查询每一个部门排名前三的工作年限 ####

#### 3.查询每一个部门的排名前三的工薪水平（不同员工同一薪资，只显示一位） ####

### 四、内容： ###

#### 1.对全部员工根据薪酬水平进行从高到低排序 ####

拿数组(56,56,67,67,67,78,79,80)来说，可以形成三种排列：  
（1）即使值相同，也会不会排在同一位:(1,2,3,4,5,6,7,8,)  
（2）值相同的在同一位，但不占位:(1,1,2,2,2,3,4,5)  
（3）值相同的在同一位，但占位:(1,1,3,3,3,6,7,8)

##### （1）薪酬水平相同时，按照员工工号排序 #####

Select 
    EmployeeID, 
    InCome, 
    @rowNum := @rowNum+1 AS Rank 
    FROM (SELECT @rowNum := 0)r, 
    Salary 
    ORDER BY InCome DESC

这里的@rowNum 是自定义的一个变量，@rowNum := @rowNum+1 代表一个首项为0，等差为1的自增列r，每查询到一个InCome(即薪水)，就会@rowNum这个变量就会递增1，放到r这个自增列中。  
也可以简单地把（SELECT @rowNum := 0）r看成是一个新的表，整体的查询相当于在Salary这个原来的表与新的r表进行复合表查询  
但使用这种方法，在遇到薪酬水平相同时，会出现排位不相同的现象  
![Mysql排序][Mysql]

如图中第一名与第二名的薪水相同但排名不一致

##### （2）薪酬水平相同时，排位相同，但不占位排序 #####

当需要对相同值进行相同排序时，可以使用多一个变量来使得相同薪酬水平的人排位是一致的

SELECT
      EmployeeID,
      InCome,
      @rowNum := @rowNum + (@prev <> (@prev := InCome)) Rank
    FROM
      Salary,
      (SELECT @rowNum := 0, @prev := -1) init
    ORDER BY InCome desc

这里的@prev变量是用作对比前一个InCome和下一个InCome是否一致，(@prev <>(@prev:=InCome))的作用在于判断是否需要给@rowNum递增一位：  
①如果前一个员工的InCome的薪酬水平和当前查询的员工的InCome薪酬水平是相同的，那么@prev<>(@prev:=InCome)会返回FALSE，即0，那么当前员工的排位就会与前一个员工排位一样；  
②如果前一个员工的薪酬水平（InCome）和后一个员工的薪酬水平(InCome)不相同，那么@prev<>(@prev:=InCome)会返回TRUE，即1，那么当前员工的排位就会比前一个员工的排位多1；  
![Mysql排序][Mysql 1]  
在图中可以看出，第一名有两位，第二、三…名继续往后排

##### （3）薪酬水平相同时，排位相同且占位排序 #####

也存在某些情况，值相同时，排位相同，但接下来的排位按照正常排序排列，就拿数组(56,56,67,67,67,78,79,80)来说，可以形成占位排序:(1,1,3,3,3,6,7,8)；此时需要根据上一题作出一些调整，多生成一个变量

SELECT InCome, Rank FROM(
    SELECT
      InCome,
      @rowNum := @rowNum + 1,
      CASE
      WHEN @prev = InCome THEN @rank := @rank
      WHEN @prev <> (@prev := InCome) THEN @rank := @rowNum
      END AS Rank
    FROM
      Salary,
      (SELECT @rowNum := 0, @prev := (SELECT InCome FROM Salary ORDER BY InCome DESC LIMIT 0,1), @rank := 1) init
    ORDER BY InCome DESC
    )T

这里存在三个变量，前两个变量与第二题一样，@rowNum是用作作为递增列，@prev是用作对比前后值是否一致，而增加@rank变量的具体思路是：  
①如果前一名员工与当前员工的薪酬水平不一致，@rank会与@rowNum的值一致，一直递增，返回给Rank  
②如果前一名员工与当前员工的薪酬水平一致，@rank会返回当前@rank的值，无论有多少个员工的薪酬水平一致，都会返回相同的@rank值给Rank  
因此可以写出这样一个条件语句：

CASE
      WHEN @prev = InCome THEN @rank := @rank
      WHEN @prev <> (@prev := InCome) THEN @rank := @rowNum
      END AS Rank

而同时需要注意的是@prev的初始值应该是按照薪酬水平降序排列的第一条记录值：

@prev := (
      SELECT InCome 
      FROM Salary 
      ORDER BY InCome DESC 
      LIMIT 0,1)

![Mysql占位排序][Mysql 2]

#### 2.查询每一个部门排名前三的工作年限（组内排名） ####

SELECT DepartmentName, Name, WorkYear
    FROM Employees 
    LEFT JOIN departments USING(DepartmentID)
    WHERE(
       SELECT COUNT(DISTINCT(E.WorkYear)) FROM Employees AS E
       WHERE E.DepartmentID = Employees.DepartmentID 
       AND Employees.WorkYear < E.WorkYear) < 2
       ORDER BY DepartmentID, WorkYear

这个是稍微复杂一点的嵌套查询，由于Mysql的GROUP BY函数只能返回1行，所以需要用到其他方法去达到组内排名的目的。  
首先子查询如下：

SELECT COUNT(DISTINCT(E.WorkYear)) FROM Employees AS E
       WHERE E.DepartmentID = Employees.DepartmentID 
       AND Employees.WorkYear < E.WorkYear

这个子查询是当外层查询查询到一个Employees表中的一行数据(假设为A)时，会传递当前行的数据到子查询中，使得子查询遍历部门号与当前数据部门号(A.DepartmentID)相同的数据，查询到遍历表中每一行数据与当前数据的工作年龄（A.WorkYear）相比更大的行有多少条  
以图表的形式解释：  
![Mysql排序][Mysql 3]  
①假设当前外层查询到第一行（1，“张扬“， 2），那么会传递到子查询中，与部门编号都为1的员工比较工龄，可以看出有4位员工的工龄是比张扬要大的。  
②假设当前外层查询到第四行（1，“李丽”，6），那么会传递到子查询中，与部门编号都为1的员工比较工龄，可以看出没有员工（0<2）的工龄比李丽要大的，那么李丽的这条数据符合条件，会输出到外层的查询中；

外层

SELECT DepartmentName, Name, WorkYear
    FROM Employees 
    LEFT JOIN departments USING(DepartmentID)
    WHERE(#子查询) < 2
       ORDER BY DepartmentID, WorkYear

此层子查询用于判断数据的条数是否符合条件，接上文中子查询：  
①当由于第一行（1，“张扬“， 2）最终输出4，4>2，那么张扬这条数据就不符合条件，不会输出；  
②当由于第四行（1，“李丽”，6）最终输出0，0<2，那么李丽的这条数据符合条件，会输出结果；

![Mysql排序][Mysql 4]

#### 3.查询每一个部门的排名前三的工薪水平（不同员工同一薪资，只显示一位） ####

具体的思路和第2题是一致的，但是由于要连接3个表，看起来有点复杂，但是其实是一样的做法：

SELECT D.DepartmentName, E.Name, S.InCome 
    FROM Employees E
    JOIN Salary S USING(EmployeeID)
    JOIN Departments D USING(DepartmentID)
    WHERE(
       SELECT COUNT(DISTINCT(S2.InCome)) 
       FROM Employees E2
       JOIN Salary S2 USING(EmployeeID)
       WHERE S2.InCome > S.InCome
       AND E.DepartmentID = E2.DepartmentID) < 3
       ORDER BY E.DepartmentID

首先，从子查询入手：

SELECT COUNT(DISTINCT(S2.InCome)) 
       FROM Employees E2
       JOIN Salary S2 USING(EmployeeID)
       WHERE S2.InCome > S.InCome
       AND E.DepartmentID = E2.DepartmentID

由于薪资和员工不在同一个表中，所以子查询中需要用连接来查询；这个子查询是当外层查询查询到一个Employees表、Salary表的复合表（假设表T）中的一行数据(假设为B)时，会传递当前行的数据到子查询中，使得子查询遍历部门号与当前数据部门号(B.DepartmentID)相同的数据，查询到遍历表中每一行数据与当前数据的薪酬（B.InCome）相比更大的行有多少条  
以图表的形式解释：  
![MySQL排序][MySQL]  
上图为DepartmentID=’1’时的表T（Employees和Salary表的复合表）  
①假设外层查询得到第一行数据（“1”，“张扬”，5633.00），那么会传递到子查询中，与部门编号都为1的员工比较薪水，可以看出有2位员工的薪酬是比张扬的薪酬要高的。  
②假设外层查询到第二行数据（“1”，“伍容华”，2100.80），那么会传递到子查询中，与部门编号都为1的员工比较薪水，可以看出有5位员工的薪酬是比张扬的薪酬要高的。

外层查询：

SELECT D.DepartmentName, E.Name, S.InCome 
    FROM Employees E
    JOIN Salary S USING(EmployeeID)
    JOIN Departments D USING(DepartmentID)
    WHERE(#子查询) < 3
    ORDER BY E.DepartmentID

此层子查询用于判断数据的条数是否符合条件，接上文中子查询：  
①当由于第一行（”1”，”张扬”， 2）最终输出2，2<3，那么张扬这条数据就符合条件，会输出；  
②当由于第二行（”1”，”伍容华”，2100.80）最终输出5，5>2，那么伍容华的这条数据不符合条件，不会输出结果；

### 总结： ###

##### 1.在Mysql的排序当中，由于没有类似的函数来计算行序号，总的排位需要用到自增列的方式来排，而且自增列的方式有多种，就拿数组(56,56,67,67,67,78,79,80)来说，可以形成三种排列： #####

（1）即使值相同，也会不会排在同一位:(1,2,3,4,5,6,7,8,)  
（2）值相同的在同一位，但不占位:(1,1,2,2,2,3,4,5)  
（3）值相同的在同一位，单占位:(1,1,3,3,3,6,7,8)

##### 2.在Mysql的排序当中，需要查询到排名前N位的数据，需要结合嵌套查询以及COUNT（）函数，不能单纯使用GROUP BY，因为GROUP BY只能返回一条数据 #####

[Mysql]: /images/20220709/07d7a645d7724d3792579b7d9873164b.png
[Mysql 1]: /images/20220709/bada825247474547bd5ac774e69307da.png
[Mysql 2]: /images/20220709/1f75287ba8be493fa435076772954ae2.png
[Mysql 3]: /images/20220709/9e101fcd82e74a8eb4b77ba949248207.png
[Mysql 4]: /images/20220709/5fe8e815ccc348d2b0536f2c6b47638b.png
[MySQL]: /images/20220709/e53cb455586443be8cb04da3818f693a.png