# SQL (LeetCode、HackerRank)
- [w3schools:SQL](https://www.w3schools.com/sql/default.asp)
- [SQL語法教學](https://www.1keydata.com/tw/sql/sql.html)
- [runoob](http://www.runoob.com/sql/sql-orderby.html)
> - https://dotblogs.com.tw/joysdw12/2011/12/28/63596
> > [SQL] 於多筆重複資料中取得該重複群組中最新一筆資料)
```
THIS WORKED on MySQL
```
```
SQL Syntax Running Priority
FROM -> ON -> JOIN -> WHERE -> GROUP BY ->
WITH -> HAVING -> SELECT -> DISTINCT -> ORDER BY
```
#### 更複雜的SQL語法
- [進階SQL](https://www.1keydata.com/tw/sql/advanced.html)
- 先前公司資料庫未完全正規化，所以還是會有主鍵重覆值的問題，因為要轉拋資料到另外一個資料庫，所以在編寫SQL時"盡可能"撈出資料未有重覆情況，若是在SQL方面可解決，就不會用程式去一一比對處理反而效能受到影響。後續若還是無法將重覆情況順利塞到另一資料表，直接使用"Update data if it already exists INSERT if it does not exist".”
``` SQL=
INSERT INTO...ON DUPLICATE KEY UPDATE
```
``` SQL=
SELECT a.Type , a.Qty, a.Amount
FROM OrderSummary AS a
WHERE (a.Type, a.Qty) IN
(SELECT Type,MAX(Qty)
FROM OrderSummary
GROUP BY Type);
```
#### 175. Combine Two Tables (LEFT JOIN)
- 透過資料表pserson的主鍵PersonID與Address的PersonID left join on
```SQL=
# Write your MySQL query statement below
Select p.FirstName as FirstName, p.LastName as LastName, a.City as City, a.State as State
from Person as p
left join Address as a
on p.PersonId = a.PersonId;
```
#### 176. Second Highest Salary ()
- 第一方法：先用(select max(Salary) from Employee)找出最大的salary，與每個Salary比較後會找到第二大的，之後透過select max找出最大值，簡單來說就是先把第一大的排除。
```SQL=
select max(Salary) as SecondHighestSalary
from Employee
Where Salary < (select max(Salary) from Employee);
```
- 第二種方法：用ORDER BY DECS Limit 1 降序取最大值做比較
```SQL=
select max(Salary) as SecondHighestSalary
from Employee
Where Salary < (select Salary from Employee ORDER BY Salary Desc limit 1);
```
#### 178. Rank Scores
- 要找出S2中比S1大的Score且不重覆，因為最高值找到的個數為0，需要將count出來的值+1做AS Rank，最後用ORDER BY DESC
```SQL=
# Write your MySQL query statement below
SELECT S1.Score AS Score , (SELECT Count(DISTINCT(Score))
FROM Scores AS S2
WHERE S2.Score > S1.Score ) + 1 AS Rank
FROM Scores AS S1
ORDER BY S1.Score DESC
;
```
#### 180. Consecutive Numbers
- 透過as三個資料表做比較，採取Id + 1 方式，在比較是否為三次連續的值，最後用distinct過濾重覆值將值選出。
```SQL=
# Write your MySQL query statement below
select distinct(L1.Num) as ConsecutiveNums
from Logs as L1, Logs as L2, Logs as L3
WHERE L1.Id + 1 = L2.Id and L2.Id + 1 = L3.Id and L1.Num = L2.Num and L2.Num = L3.Num;
```
#### 181. Employees Earning More Than Their Managers
- 第一種：主要找到員工比管理者薪水多的，所以AS出兩個資料表e1,e2，在用where找出哪個員工是被管理的，並且薪資是大於e2.Id的
```SQL=
SELECT e1.Name as Employee
FROM
Employee AS e1,
Employee AS e2
WHERE
e1.ManagerId = e2.Id
AND e1.Salary > e2.Salary
;
```
- 第二種 使用LEFT JOIN As e2 ON 哪個員工是被管理的e1.ManagerId = e2.Id，Where薪資是大於e2.Id的
```SQL=
SELECT e1.Name As Employee
FROM
Employee As e1
LEFT JOIN
Employee As e2
ON
e1.ManagerId = e2.Id
WHERE
e1.Salary > e2.Salary
;
```
#### 182. Duplicate Emails
- 第一種：先用GROUP BY群組後用Having 篩選並使用聚合函數找出count(email)大於1的
```SQL=
# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1
;
```
- 第二種：先as兩個資料表後，用WHERE篩選出email相同但id不同的，這種方法是self join。也可以用 (INNER JOIN Person as P2)或是單純JOIN
```SQL=
SELECT DISTINCT p1.Email
FROM Person AS p1, Person AS p2
WHERE p1.Email = p2.Email and p1.id != p2.id
;
```
#### 183. Customers Who Never Order
- 第一種：使用LEFT JOIN orders on C.id = O.CustomerId，在用WHERE 找出O.Id是Null的客戶。
```SQL=
SELECT C.Name as Customers
FROM Customers as C
LEFT JOIN Orders as O
ON C.Id = O.CustomerId
WHERE O.Id IS NULL
;
```
- 第二種：直接用WHERE找出C.Id沒有在在Orders中的CId。
```SQL=
SELECT Customers.Name as Customers
FROM Customers
WHERE Customers.Id NOT IN
(
SELECT CustomerId FROM Orders
);
```
#### 184. Department Highest Salary
- 將兩個資料表做JOIN後，使用GROUP BY 群組後找出薪資MAX最大的員工，用WHERE (e.DepartmentId, e.Salary) IN 若是在此資料表中則，將值帶出。
```SQL=
SELECT D.Name as Department, E.Name as Employee, E.Salary as Salary
FROM Employee as e
JOIN Department as D
ON e.DepartmentId = D.Id
WHERE (e.DepartmentId, e.Salary) IN
(SELECT DepartmentId, MAX(Salary)
FROM Employee
GROUP BY DepartmentId
)
;
```
#### 185. Department Top Three Salaries （hard)
- 先JOIN(INNER JOIN也可)兩個資料表依靠DepartmentId（外部索引）連結，E2.Salary中比E1的資料表找出比E1.Salary（1 | Joe | 85000 | 1 ）薪水還高的Salary有幾位，並且是在同一個部門，因為要在每個部門找出前三高的，所以需要過濾重覆Salary做計數後的值要小於3。（若找出來數值是0那就代表是第一高薪的，若count出來大於3則代表非前三高排除）
```SQL=
SELECT D.Name AS Department, E1.Name AS Employee, E1.Salary AS Salary
FROM Employee AS E1
JOIN Department AS D
ON E1.DepartmentId = D.Id
WHERE 3 > ( SELECT count(DISTINCT(Salary))
FROM Employee AS E2
WHERE E2.DepartmentId = E1.DepartmentId AND E2.Salary > E1.Salary
)
;
```
#### 196. Delete Duplicate Emails
- 找出重覆值並刪除，若P1.Id 比 P2.Id 大 則代表此紀錄是後續新增的
```SQL=
DELETE P1
FROM Person as P1, Person as P2
WHERE P1.Email = P2.Email AND P1.Id > P2.Id
;
```
#### 197. Rising Temperature
- 先self JOIN AS W2,使用DATEDIFF去找出相差是正數1的日期（代表是第二天-第一天），並且第二天的溫度比第一天的溫度高。
```SQL=
SELECT W1.Id
FROM Weather AS W1
JOIN Weather AS W2
WHERE DATEDIFF(W1.RecordDate, W2.RecordDate) = 1 AND W1.Temperature > W2.Temperature
;
```
#### 262. Trips and Users (Hard)
```SQL=
```
#### 595. Big Countries
```SQL=
SELECT name, population, area
FROM World
WHERE area > 3000000 OR population > 25000000;
```
#### 596. Classes More Than 5 Students
- 先將class做群組，用HAVING 找出每門課的學生人數>=5的課程，並且學生是不能重覆的。
```SQL=
SELECT class
FROM courses
GROUP BY class
HAVING count(DISTINCT student) >= 5
;
```
#### 601. Human Traffic of Stadium
- AS 三個資料表，先找出people>=100，因為要找出連續三天以上人數都>=100的，所以需要對id進行判斷，三種條件符合其中一項即為正確。
```SQL=
# Write your MySQL query statement below
SELECT DISTINCT S1.id , S1.visit_date , S1.people
FROM stadium AS S1, stadium AS S2, stadium AS S3
WHERE S1.people >= 100 AND S2.people >= 100 AND S3.people >=100 AND
((S1.id + 1 = S2.id AND S1.id + 2 = S3.id)
OR
(S1.id - 1 = S2.id AND S1.id + 1 = S3.id)
OR
(S1.id - 1 = S2.id AND S1.id - 2 = S3.id))
ORDER BY S1.id
;
```
#### 627. Swap Salary
- 當sex等於f則換成m，若不等於f則換成f
```SQL=
update salary
SET sex=(IF(sex='f','m','f'));
```
###### tags: `SQL`
Sign in with Wallet
Connect another wallet