DB學習筆記 — SQL刷題002

SeanChien

11 min readMar 10, 2021

前言

來到了DB學習筆記SQL篇的第二篇，這次一樣是Leetcode的練習題，難度有比上次的稍微提高，一樣有把不同的寫法都寫進來，希望對大家有所幫助。

附上之前兩篇的傳送門，還沒讀完的朋友們可以點擊下方連結去看看喔～
🚪 DB學習筆記 — 基本概念/安裝資源
🚪 DB學習筆記 — SQL刷題001

話不多說，我們直接進入主題！

目錄

Leetcode 180 — Consecutive Numbers⭐️⭐️
Leetcode 184 — Department Highest Salary⭐️⭐️⭐️
Leetcode 185 — Department Top Three Salaries⭐️⭐️⭐️⭐️

可以透過Ctrl / Command + F 來進行搜尋，找到自己有興趣的題目去練習。

Leetcode 180— Consecutive Numbers⭐️⭐️

題目敘述：

Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order.

解題思路：

從題目中的敘述可以知道，我們要找的重點是Num這個欄位，然後去觀察三個相連的Id他的Num，以上面的範例來說Id1,2,3的Num都是1，所以答案會包含1這個數字。

📌解法一：那這樣答案就呼之欲出了，我們可以將Logs這張表JOIN三次，並用Num作為匹配的值，然後第一張表的Id是第二張表的Id-1，第二張表的Id是第三表的Id-1這樣的匹配邏輯，最後再透過Distinct Num將結果回傳即可！

SELECT DISTINCT A.Num as ConsecutiveNums
FROM Logs A 
JOIN Logs B 
ON A.Num=B.Num 
JOIN Logs C
ON B.Num=C.Num 
WHERE A.Id = B.Id-1
AND B.Id = C.Id-1

其實這樣的串接方式會產生像這樣的表格

#上面的結果會產生這樣的圖表，然後取出A.Num就是結果----------------------------------------------
| A.ID | A.Num | B.ID | B.Num | C.ID | C.Num |
----------------------------------------------
|   1  |   1   |   2  |   1   |   3  |   1   |
----------------------------------------------#如果今天Id 1,2,3,4都 Num都是1的話，會回傳下面的表----------------------------------------------
| A.ID | A.Num | B.ID | B.Num | C.ID | C.Num |
----------------------------------------------
|   1  |   1   |   2  |   1   |   3  |   1   |
----------------------------------------------
|   2  |   1   |   3  |   1   |   4  |   1   |
----------------------------------------------#這時候發現A.Num會有兩個1，這就是為什麼要用Distinct的原因

📌解法二：當然，也可以用我們可以將Logs這張表JOIN三次，第一張表的Id是第二張表的Id-1作為匹配值，第二張表的Id是第三張表的Id-1作為匹配，然後找出Num相等的值，最後一樣再透過Distinct Num將結果回傳！

SELECT distinct(A.Num) As ConsecutiveNums 
FROM Logs A 
JOIN Logs B
ON A.Id = B.Id-1 
JOIN Logs C
ON B.Id = C.Id-1
WHERE A.Num=B.Num
AND B.Num=C.Num

基本上這兩個解法的邏輯差不多，只是寫法上有點差異，那我們的哩扣一八零就到這邊，接下來是更難的題目！

Leetcode 184 — Department Highest Salary⭐️⭐️⭐️

題目敘述：

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

解題思路：

從題目可以知道，我們必須先得知各個部門最高薪的薪水是多少，如果該部門的員工薪水是那個薪水，那我們就把答案回傳。

📌解法一：首先，我們透過JOIN將Employee表跟Department表串在一起，同時我們找到(部門,該部門最高薪水)並透過WHERE來比對，最後就可以將結果給找到

📍要找到(部門,該部門最高薪水)就跟之前在DB學習筆記 — SQL刷題001中的Leetcode 176 — Second Highest Salary 的概念大同小異，還沒看過的朋友可以先去看看。

SELECT D.name AS Department,
       E.Name AS Employee, 
       Salary
FROM Employee E
JOIN Department D
ON E.DepartmentId = D.Id
WHERE (DepartmentId,Salary) in
(SELECT DepartmentId, Max(Salary)
FROM  Employee 
GROUP BY DepartmentId)

📌解法二：跟解法一的邏輯大同小異，只是透過CTE的方式來撰寫，除了有更好的可讀性，也更好做修改。另外這邊的做法不是將(部門,該部門最高薪水)做直接做比對，而是將兩個表個的部門及薪水分開做比對，其實概念是一樣的啦，只是寫法上的差異。

WITH Detail AS
(SELECT D.Name AS Department,
        E.Name AS Employee, 
        E.Salary AS Salary
 FROM Employee E 
 JOIN Department D
 ON E.DepartmentId = D.Id),Top as 
(SELECT Max(Salary) as Max_Salary,
        Department 
 FROM Detail 
 GROUP BY Department)
 
SELECT D.Department, Employee, D.Salary
FROM Detail D
JOIN Top T
WHERE D.Department = T.Department
AND D.Salary = T.Max_Salary

📌解法三：這邊就用到了Window Function來解答，這也是我最喜歡的方法，因為寫起來非常簡潔，並且在下一題可以快速得到答案。首先，將兩個表進行JOIN，並透過DENSE_RANK()將排名寫出來，然後取出Department, Employee, Salary這三個欄位，並且DENSE_RANK()等於1，這樣就會找到答案。

SELECT Department, Employee, Salary
FROM (SELECT D.name AS Department,
             E.Name AS Employee, 
             Salary,
             DENSE_RANK() OVER (PARTITION BY D.id 
                                ORDER BY E.salary DESC) AS Ranked
      FROM Employee E
      JOIN Department D
      ON E.DepartmentId = D.Id) AS Ans
WHERE Ranked = 1

當然也可以將寫在FROM裡面的這一串SubQuery透過CTE的方式來寫，來維持版面的可讀性。

WITH Ans as (
            SELECT D.name AS Department,
                   E.Name AS Employee, 
                   Salary,
                   DENSE_RANK() OVER (PARTITION BY D.id 
                                      ORDER BY E.salary DESC) AS     
                                                              Ranked
            FROM Employee E
            JOIN Department D
            ON E.DepartmentId = D.Id)
SELECT Department, Employee, Salary
FROM Ans
WHERE Ranked = 1

然後就可以把上面的寫法進行簡單的更改就可以完成下一題了！

Leetcode 185 — Department Top 3 Salaries⭐️⭐️⭐️⭐️

題目敘述：

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 (order of rows does not matter).

解題思路：

這題的解題其實跟上一題是差不多的，只是要回傳前三名，那這時候只要將解法三的Ranked稍微更改答案就呼之欲出了。

📌解法一：將上一題的Ranked=1改成Ranked<4，就可以得到前三名的結果。

SELECT Department, Employee, Salary
FROM (SELECT D.name AS Department,
             E.Name AS Employee, 
             Salary,
             DENSE_RANK() OVER (PARTITION BY D.id 
                                ORDER BY E.salary DESC) AS Ranked
      FROM Employee E
      JOIN Department D
      ON E.DepartmentId = D.Id) AS Ans
WHERE Ranked < 4

當然，一樣可以透過CTE的方式讓程式碼更簡潔！

WITH Ans as (
            SELECT D.name AS Department,
                   E.Name AS Employee, 
                   Salary,
                   DENSE_RANK() OVER (PARTITION BY D.id 
                                      ORDER BY E.salary DESC) AS     
                                                              Ranked
            FROM Employee E
            JOIN Department D
            ON E.DepartmentId = D.Id)SELECT Department, Employee, Salary
FROM Ans
WHERE Ranked < 4

而且這題在Leetcode中的難度是Hard，透過上述的算法，還得到很高的效能跟速度！