SQL實現LeetCode(184.系裡最高薪水)
[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表，讓我們找系裡面薪水最高的人的，實際上這題是Second Highest Salary和Combine Two Tables的結合題，我們既需要聯合兩表，又要找到最高薪水，那麼我們首先讓兩個表內交起來，然後將結果表需要的列都標明，然後就是要找最高的薪水，我們用Max關鍵字來實現，參見代碼如下：
解法一：
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關鍵字，直接用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關鍵字，而是用瞭>=符號，實現的效果跟Max關鍵字相同，參見代碼如下：
解法三：
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
到此這篇關於SQL實現LeetCode(184.系裡最高薪水)的文章就介紹到這瞭,更多相關SQL實現系裡最高薪水內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！