各種選取特殊值的綜合應用
選取一些特殊值如最大值、最小值、連續值、缺失值、重復值等
一般來說，選取工作年限最高的人，在MySQL中可以簡單地用MAX()函數求出，但當要同時輸出該人的姓名，則容易出錯，Mysql會直接選取各個部門中第一行數據的人的姓名，而非對應工作年限最高的人，如下：
SELECT Name, MAX(WorkYear), DepartmentNameFROM Employees LEFT JOIN Departments USING(DepartmentID)GROUP BY DepartmentID得出的結果是： 
然而回顧一下Employees表，名字為“張揚”的員工，工作年齡只是2年而非6年 
因此為了防止這類型錯誤，需要使用嵌套查詢
SELECT Name, DepartmentName, Employees.WorkYearFROMEmployees, Departments,(SELECT MAX(WorkYear) AS WorkYear, DepartmentId FROM Employees GROUP BY DepartmentId)TWHERE Employees.WorkYear = T.WorkYearAND Departments.DepartmentID = T.DepartmentIDAND Departments.DepartmentID = Employees.DepartmentIDORDER BY DepartmentName這里的子查詢用于確定每個部門最高的工作年限：
SELECT MAX(WorkYear) AS WorkYear, DepartmentId FROM Employees GROUP BY DepartmentId確定了薪酬水平后，再從Employees表中找出該薪酬水平對應的人的名稱以及部門ID，再通過部門ID在部門表中找到部門名稱：
SELECT Name, DepartmentName, Employees.WorkYearFROMEmployees, Departments,(#子查詢)TWHERE Employees.WorkYear = T.WorkYearAND Departments.DepartmentID = T.DepartmentIDAND Departments.DepartmentID = Employees.DepartmentIDORDER BY DepartmentName
同理，選取各個部門的薪水最低的人的姓名及其薪酬，使用的也是自嵌套查詢而不能直接查詢，而由于這里涉及三個表，則需要連接三個表進行嵌套查詢
SELECT S1.InCome, Name, DepartmentNameFROM Employees E1LEFT JOIN Salary S1 USING(EmployeeID)LEFT JOIN Departments D USING(DepartmentID),(SELECT MIN(InCome) AS InCome, DepartmentIDFROM Salary S2LEFT JOIN Employees E2 USING(EmployeeID)GROUP BY E2.DepartmentID)TWHERE T.DepartmentID = E1.DepartmentIDAND T.InCome = S1.InCome原理與第一題相同，在子查詢部分先查詢到各個部門中薪水最低的薪水水平是多少
SELECT MIN(InCome) AS InCome, DepartmentIDFROM Salary S2LEFT JOIN Employees E2 USING(EmployeeID)GROUP BY E2.DepartmentID然后把結果輸出到外層，進行比較
SELECT S1.InCome, Name, DepartmentNameFROM Employees E1LEFT JOIN Salary S1 USING(EmployeeID)LEFT JOIN Departments D USING(DepartmentID),(#子查詢)TWHERE T.DepartmentID = E1.DepartmentIDAND T.InCome = S1.InCome得出結果： 
由于部門經理也是員工之一，因此需要連接部門經理以及員工表，以作對比
SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM(SELECT DepartmentID, Name, InComeFROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID))ELEFT JOIN(SELECT M2.DepartmentID, M2.EmployeeID, E2.Name, InCome FROM Managers M2LEFT JOIN Salary S2 USING(EmployeeID)LEFT JOIN Employees E2 USING(EmployeeID))MUSING(DepartmentID)WHERE E.InCome > M.InComeORDER BY M.DepartmentID①首先，需要連接Employees和Salary兩個表，成為E表，代表全體員工的薪酬水平
(SELECT DepartmentID, Name, InComeFROM Employees E1 LEFT JOIN Salary S1 USING(EmployeeID))E得出以下的表： 
②然后需要連接Managers和Salary表，成為M表，代表各個部門經理的薪酬水平
得出以下的表： 
然后把E表和M表通過相同的部門編號進行連接
SELECT E.Name AS Employee, E.InCome, M.Name AS Manager, M.InCome FROM(#全體員工的薪酬水平)ELEFT JOIN(#各個部門經理的薪酬水平)MUSING(DepartmentID)WHERE E.InCome > M.InComeORDER BY M.DepartmentID通過對比，可以得出結果： 
使用ISNULL( )函數可以直接找出還未錄入薪酬的員工名稱
SELECT NameFROM EmployeesLEFT JOIN Salary USING(EmployeeID)WHERE ISNULL(InCome)
選取重復的電話號碼時，可以選擇通過電話號碼來連接兩個Employees表，且連接后，員工的ID是不一樣的，則可以選擇不同員工的重復的電話號碼
SELECT DISTINCT E1.Name, E1.PhoneNumberFROM Employees E1 LEFT JOIN Employees E2 USING(PhoneNumber)WHERE E1.EmployeeID <> E2.EmployeeID
新聞熱點
疑難解答
