[LeetCode] 184.Department Highest Salary 系里最高薪水

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

這道題讓給了我們兩張表，Employee表和Department表，讓我們找系里面薪水最高的人的，實(shí)際上這題是Second Highest Salary和Combine Two Tables的結(jié)合題，我們既需要聯(lián)合兩表，又要找到最高薪水，那么我們首先讓兩個(gè)表內(nèi)交起來，然后將結(jié)果表需要的列都標(biāo)明，然后就是要找最高的薪水，我們用Max關(guān)鍵字來實(shí)現(xiàn)，參見代碼如下：

解法一：

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary FROM Employee e1
JOIN Department d ON e1.DepartmentId = d.Id WHERE Salary IN 
(SELECT MAX(Salary) FROM Employee e2 WHERE e1.DepartmentId = e2.DepartmentId);

我們也可以不用Join關(guān)鍵字，直接用Where將兩表連起來，然后找最高薪水的方法和上面相同：

解法二：

SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e, Department d
WHERE e.DepartmentId = d.Id AND e.Salary = (SELECT MAX(Salary) FROM Employee e2 WHERE e2.DepartmentId = d.Id);

下面這種方法沒用用到Max關(guān)鍵字，而是用了>=符號(hào)，實(shí)現(xiàn)的效果跟Max關(guān)鍵字相同，參見代碼如下：

解法三：

SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e, Department d
WHERE e.DepartmentId = d.Id AND e.Salary >= ALL (SELECT Salary FROM Employee e2 WHERE e2.DepartmentId = d.Id);

類似題目：

Second Highest Salary

Combine Two Tables

到此這篇關(guān)于SQL實(shí)現(xiàn)LeetCode(184.系里最高薪水)的文章就介紹到這了,更多相關(guān)SQL實(shí)現(xiàn)系里最高薪水內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

美國穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。