MYSQL基础上机练习题（七）选取最大值、最小值、连续值、缺失值、重复值的综合应用

爱被打了一巴掌 2022-09-26 15:52 202阅读 0赞

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

熟悉Mysql中选取某些特殊值的方法，如组内最大值、组内最小值、连续值、缺失值、重复值等

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

#### 1.选取组内最大值、组内最小值 ####

#### 2.选取标准值上下值 ####

#### 3.选取连续出现的值 ####

#### 4.选取缺失值 ####

#### 5.选取重复值 ####

### 三、题目： ###

#### 1.选取各部门工作年限最高的人的姓名以及其工作年限 ####

#### 2.选取各部门薪水最低的人的姓名以及其薪酬水平 ####

#### 3.选取各个部门中每个部门的员工比部门经理薪水高的人的姓名及其薪酬水平 ####

#### 4.选取企业中连续迟到6天的人 ####

#### 5.选取还未录入薪酬水平的员工名称 ####

#### 6.选取电话号码重复的员工姓名及其电话号码 ####

### 四、内容： ###

#### 1.选取各部门工作年限最高的人的姓名以及其工作年限 ####

一般来说，选取工作年限最高的人，在Mysql中可以简单地用MAX()函数求出，但当要同时输出该人的姓名，则容易出错，Mysql会直接选取各个部门中第一行数据的人的姓名，而非对应工作年限最高的人，如下：

SELECT Name, MAX(WorkYear), DepartmentName
    FROM Employees 
    LEFT JOIN Departments USING(DepartmentID)
    GROUP BY DepartmentID

得出的结果是：  
![MYSQL最大值查询][MYSQL]  
然而回顾一下Employees表，名字为“张扬”的员工，工作年龄只是2年而非6年  
![MYSQL最大值查询][MYSQL 1]

因此为了防止这类型错误，需要使用嵌套查询

SELECT Name, DepartmentName, Employees.WorkYear
    FROM
    Employees, 
    Departments,
    (SELECT 
    MAX(WorkYear) AS WorkYear,  
    DepartmentId 
    FROM Employees GROUP BY DepartmentId)T
    WHERE Employees.WorkYear = T.WorkYear
    AND Departments.DepartmentID = T.DepartmentID
    AND Departments.DepartmentID = Employees.DepartmentID
    ORDER BY DepartmentName

这里的子查询用于确定每个部门最高的工作年限：

SELECT 
    MAX(WorkYear) AS WorkYear,  
    DepartmentId 
    FROM Employees GROUP BY DepartmentId

确定了薪酬水平后，再从Employees表中找出该薪酬水平对应的人的名称以及部门ID，再通过部门ID在部门表中找到部门名称：

SELECT Name, DepartmentName, Employees.WorkYear
    FROM
    Employees, 
    Departments,
    (#子查询)T
    WHERE Employees.WorkYear = T.WorkYear
    AND Departments.DepartmentID = T.DepartmentID
    AND Departments.DepartmentID = Employees.DepartmentID
    ORDER BY DepartmentName

![MYSQL选取最大值][MYSQL 2]

#### 2.选取各部门薪水最低的人的姓名以及其薪酬水平 ####

同理，选取各个部门的薪水最低的人的姓名及其薪酬，使用的也是自嵌套查询而不能直接查询，而由于这里涉及三个表，则需要连接三个表进行嵌套查询

SELECT S1.InCome, Name, DepartmentName
    FROM Employees E1
    LEFT JOIN Salary S1 USING(EmployeeID)
    LEFT JOIN Departments D USING(DepartmentID),
    (SELECT 
    MIN(InCome) AS InCome, 
    DepartmentID
    FROM Salary S2
    LEFT JOIN Employees E2 USING(EmployeeID)
    GROUP BY E2.DepartmentID)T
    WHERE T.DepartmentID = E1.DepartmentID
    AND T.InCome = S1.InCome

原理与第一题相同，在子查询部分先查询到各个部门中薪水最低的薪水水平是多少

SELECT 
    MIN(InCome) AS InCome, 
    DepartmentID
    FROM Salary S2
    LEFT JOIN Employees E2 USING(EmployeeID)
    GROUP BY E2.DepartmentID

然后把结果输出到外层，进行比较

SELECT S1.InCome, Name, DepartmentName
    FROM Employees E1
    LEFT JOIN Salary S1 USING(EmployeeID)
    LEFT JOIN Departments D USING(DepartmentID),
    (#子查询)T
    WHERE T.DepartmentID = E1.DepartmentID
    AND T.InCome = S1.InCome

得出结果：  
![MYSQL选取最小值][MYSQL 3]

#### 3.选取各个部门中每个部门的员工比部门经理薪水高的人的姓名及其薪酬水平 ####

由于部门经理也是员工之一，因此需要连接部门经理以及员工表，以作对比

SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM
    (SELECT DepartmentID, Name, InCome
    FROM Employees E1 
    LEFT JOIN Salary S1 USING(EmployeeID))E
    LEFT JOIN
    (SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome 
    FROM Managers M2
    LEFT JOIN Salary S2 USING(EmployeeID)
    LEFT JOIN Employees E2 USING(EmployeeID))M
    USING(DepartmentID)
    WHERE E.InCome > M.InCome
    ORDER BY M.DepartmentID

①首先，需要连接Employees和Salary两个表，成为E表，代表全体员工的薪酬水平

(SELECT DepartmentID, Name, InCome
    FROM Employees E1 
    LEFT JOIN Salary S1 USING(EmployeeID))E

得出以下的表：  
![MYSQL对比][MYSQL 4]  
②然后需要连接Managers和Salary表，成为M表，代表各个部门经理的薪酬水平

(SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome 
    FROM Managers M2
    LEFT JOIN Salary S2 USING(EmployeeID)
    LEFT JOIN Employees E2 USING(EmployeeID))M

得出以下的表：  
![MYSQL对比应用][MYSQL 5]

然后把E表和M表通过相同的部门编号进行连接

SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM
    (#全体员工的薪酬水平)E
    LEFT JOIN
    (#各个部门经理的薪酬水平)M
    USING(DepartmentID)
    WHERE E.InCome > M.InCome
    ORDER BY M.DepartmentID

通过对比，可以得出结果：  
![MYSQL对比应用][MYSQL 6]

#### 4.选取企业中连续迟到6天的人 ####

#### 5.选取还未录入薪酬的员工名称 ####

使用ISNULL( )函数可以直接找出还未录入薪酬的员工名称

SELECT Name
    FROM Employees
    LEFT JOIN Salary USING(EmployeeID)
    WHERE ISNULL(InCome)

![MYSQL缺失值][MYSQL 7]

#### 6.选取电话号码重复的员工姓名及其电话号码 ####

选取重复的电话号码时，可以选择通过电话号码来连接两个Employees表，且连接后，员工的ID是不一样的，则可以选择不同员工的重复的电话号码

SELECT DISTINCT E1.Name, E1.PhoneNumber
    FROM Employees E1 LEFT JOIN Employees E2 USING(PhoneNumber)
    WHERE E1.EmployeeID <> E2.EmployeeID

![MYSQL选取重复值][MYSQL 8]

[MYSQL]: /images/20220708/0ad7a04601c94c4499a453f3a12254bb.png
[MYSQL 1]: /images/20220708/a5daec183f3a413496a337ea608ca892.png
[MYSQL 2]: /images/20220708/d6f20320f98e4e7f87842280df8421c7.png
[MYSQL 3]: /images/20220708/8d46244efede4355b0b0ccf41cdf07f8.png
[MYSQL 4]: /images/20220708/0d6363540da9422f8bf6c0acd13b4f12.png
[MYSQL 5]: /images/20220708/741ac9e67bcf4074b77e9da708070e79.png
[MYSQL 6]: /images/20220708/35144a34fe8e455b8fbfe321506a56de.png
[MYSQL 7]: /images/20220708/296142fb53c9467b8e6ccc7d47e4ca51.png
[MYSQL 8]: /images/20220708/40aa551c8e8b42bf9cb37af0eedda23f.png