SQL實現LeetCode(185.系裡前三高薪水)
[LeetCode] 185.Department Top Three Salaries 系裡前三高薪水
The Employee table holds all employees. Every employee has an Id, 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 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+—-+——-+——–+————–+
The Department table holds all departments of the company.
+—-+———-+
| Id | Name |
+—-+———-+
| 1 | IT |
| 2 | Sales |
+—-+———-+
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+————+———-+——–+
這道題是之前那道Department Highest Salary的拓展,難度標記為Hard,還是蠻有難度的一道題,綜合瞭前面很多題的知識點,首先看使用Select Count(Distinct)的方法,我們內交Employee和Department兩張表,然後我們找出比當前薪水高的最多隻能有兩個,那麼前三高的都能被取出來瞭,參見代碼如下:
解法一:
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e JOIN Department d on e.DepartmentId = d.Id WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE Salary > e.Salary AND DepartmentId = d.Id) < 3 ORDER BY d.Name, e.Salary DESC;
下面這種方法將上面方法中的<3換成瞭IN (0, 1, 2),是一樣的效果:
解法二:
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM Employee e, Department d WHERE (SELECT COUNT(DISTINCT Salary) FROM Employee WHERE Salary > e.Salary AND DepartmentId = d.Id) IN (0, 1, 2) AND e.DepartmentId = d.Id ORDER BY d.Name, e.Salary DESC;
或者我們也可以使用Group by Having Count(Distinct ..) 關鍵字來做:
解法三:
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM (SELECT e1.Name, e1.Salary, e1.DepartmentId FROM Employee e1 JOIN Employee e2 ON e1.DepartmentId = e2.DepartmentId AND e1.Salary <= e2.Salary GROUP BY e1.Id HAVING COUNT(DISTINCT e2.Salary) <= 3) e JOIN Department d ON e.DepartmentId = d.Id ORDER BY d.Name, e.Salary DESC;
下面這種方法略微復雜一些,用到瞭變量,跟Consecutive Numbers中的解法三使用的方法一樣,目的是為瞭給每個人都按照薪水的高低增加一個rank,最後返回rank值小於等於3的項即可,參見代碼如下:
解法四:
SELECT d.Name AS Department, e.Name AS Employee, e.Salary FROM (SELECT Name, Salary, DepartmentId, @rank := IF(@pre_d = DepartmentId, @rank + (@pre_s <> Salary), 1) AS rank, @pre_d := DepartmentId, @pre_s := Salary FROM Employee, (SELECT @pre_d := -1, @pre_s := -1, @rank := 1) AS init ORDER BY DepartmentId, Salary DESC) e JOIN Department d ON e.DepartmentId = d.Id WHERE e.rank <= 3 ORDER BY d.Name, e.Salary DESC;
類似題目:
Department Highest Salary
Second Highest Salary
Combine Two Tables
參考資料:
https://leetcode.com/discuss/23002/my-tidy-solution
https://leetcode.com/discuss/91087/yet-another-solution-using-having-count-distinct
https://leetcode.com/discuss/69880/two-solutions-1-count-join-2-three-variables-join
到此這篇關於SQL實現LeetCode(185.系裡前三高薪水)的文章就介紹到這瞭,更多相關SQL實現系裡前三高薪水內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- SQL實現LeetCode(184.系裡最高薪水)
- SQL實現LeetCode(180.連續的數字)
- MySQL如何對數據進行排序圖文詳解
- Python全棧之學習MySQL(2)
- MySQL必備基礎之分組函數 聚合函數 分組查詢詳解