動機
leetcode 上解 sql，潮
同時熟悉sql怎麼用
一些常用的工具
CTE (Common Table Expression)
就是把subquery抽到前面去
但比起subquery，cte的table可以reference to self 可以遞迴！！
window function
可以把table的部分資料割出來做計數與排名
常用的是 rank與dense_rank
兩個的差別是在重複的資料出現時 rank不會列在同一名 dense_rank會列在同一名
手法
消除重複
- group by
- window function
- distinct
列舉
- self join
void
- empty table => 所有條件式沒一個符合
- null => left join 時沒有相對應的值
把empty table轉成null
- case + exists
- max(
)
題目
沒有鎖的一共19題
175. Combine Two Tables
left join
select a.FirstName, a.LastName, b.City, b.State
from Person a
left join Address b
on a.PersonId = b.PersonId;
176. Second Highest Salary
用rank對每一區排序找出第二名 但是題目有要求如果為空要是null 但如果where找不到東西的話，整個表是空的！！
所以還要在所以還要再包一層max，如果沒東西就會自動轉成null！！
select max(a.Salary) SecondHighestSalary
from (select Salary from (select id, Salary, rank () OVER (ORDER BY Salary desc) rk from Employee) b where rk = 2) a;
177. Nth Highest Salary
上一題的延續，但是排名是要可以重複的 所以要改成dense_rank
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select max(a.Salary)
from (select Salary from (select id, Salary, dense_rank () OVER (ORDER BY Salary desc) rk from Employee) b where rk = N) a
);
END
178. Rank Scores
都寫著rank了 就是用rank 不過因為是要重複排名所以要用dense_rank
select score, dense_rank () over (order by score desc) `Rank`
from Scores;
180. Consecutive Numbers
這裡點出sql的programming想法
用組合出所有需要的資料在同一個row 再用條件篩掉 再從中產生新的資料，丟到新的table 反覆放資料到table讓下一個table使用
這裡的條件是 兩個row的
- 數字
- id 要一樣
但我們需要至少三個row同時成立 所以就join兩次這樣就有三個row了
select distinct a.Num ConsecutiveNums
from Logs a
join Logs b
on a.Num = b.Num and a.Id = b.Id+1
join Logs c
on a.Num = c.Num and a.Id = c.Id+2
181. Employees Earning More Than Their Managers
就是join
select A.Name Employee
from Employee A
join Employee B
on A.ManagerId = B.Id
where A.Salary > B.Salary
182. Duplicate Emails
group by可以把同樣的column的集合在一起 再看集合在一起的row有多少個
select Email
from Person
group by Email
having count(Email) > 1
183. Customers Who Never Order
left join才會有null!!
select name Customers
from Customers
left join Orders
on Orders.CustomerId = Customers.id
where CustomerId is null
184. Department Highest Salary
先生出有rk的表 再挑出rk是1的
with a as (
select Id, Name, Salary, DepartmentId, dense_rank () over (partition by DepartmentId order by Salary desc) rk
from Employee
)
select B.Name Department, A.Name Employee, A.Salary
from A
join Department B
on B.Id = A.DepartmentId
where A.rk = 1
185. Department Top Three Salaries
與前一題差不多 但我寫了兩個版本
ver.1
用or去組出來
with a as (
select *, dense_rank () over (partition by DepartmentId order by Salary desc) rk
from Employee
)
select b.Name Department , a.Name Employee, a.Salary
from a
join Department b on b.Id = a.DepartmentId
where a.rk =1 or a.rk = 2 or a.rk = 3
ver.2
小於3
理所當然的，這個效能比較好
with a as (
select *, dense_rank () over (partition by DepartmentId order by Salary desc) rk
from Employee
)
select b.Name Department , a.Name Employee, a.Salary
from a
join Department b on b.Id = a.DepartmentId
where a.rk <= 3
196. Delete Duplicate Emails
用rank來結合同樣的email同時標上rank 之後把rk大於1的delete就好了
with A as (select Id, ROW_NUMBER() OVER (PARTITION BY Email order by Id) AS rk from Person)
delete from Person
where Id = Any(select Id from A where A.rk > 1)
197. Rising Temperature
與180很像
select A.Id
from Weather A
join Weather B
on B.RecordDate = subdate(A.RecordDate,1)
where A.Temperature > B.Temperature
262. Trips and Users
- 把沒有ban的與在期限內的row找出來
- 分別算 同一天的總交易數 與 同一天的完成交易數
- 算比率，處理當天沒有任何完成交易的狀態
with ts as (
select Trips.Status, Trips.Request_at
from Trips
join Users a on Trips.Client_Id = a.Users_Id
join Users b on Trips.Driver_Id = b.Users_Id
where a.Banned = 'No' and b.Banned = 'No' and Trips.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
),
a as (
select *, count(*) cnt
from ts
group by Status, Request_at
),
base as (
select sum(cnt) cnt, Request_at
from a
group by Request_at
),
comp as (
select *
from a
where Status = 'completed'
)
select base.Request_at Day, IF(comp.cnt is not null, cast(1.0- (comp.cnt/base.cnt) as decimal(10,2)), 1.00) `Cancellation Rate`
from base
left join comp
on base.Request_at = comp.Request_at
595. Big Countries
複習基本sql用
select name, population ,area from World where area > 3000000 or population > 25000000
596. Classes More Than 5 Students
先用student與class group在一起 排除同一個學生在同一個class有兩個以上的學生
在group class找出題目要的東西
with a as (select *
from courses
group by student, class
)
select class
from a
group by class
having count(*) >= 5
601. Human Traffic of Stadium
與180很像但是這次要table中符合條件的row 所以如果用與180同樣的做法還要多一個步驟是 把join完的row拆回去原本的row
with a as (
select *
from stadium
where people >= 100
),
b as (
select x.id id1, x.visit_date visit_date1, x.people people1,
y.id id2, y.visit_date visit_date2, y.people people2,
z.id id3, z.visit_date visit_date3, z.people people3
from a x
join a y on x.id = y.id+1
join a z on y.id = z.id+1
),
c as (
select id1 id, visit_date1 visit_date, people1 people
from b
union all
select id2, visit_date2, people2
from b
union all
select id3, visit_date3, people3
from b
)
select distinct *
from c
order by id
620. Not Boring Movies
複習基本sql
select id, movie, description , rating from cinema where id % 2 = 1 and description != 'boring' order by rating desc
626. Exchange Seats
這題應該是所有sql題中讓我最愉悅的一題
ver.1
把奇數的id加一再把偶數的id減1 但這樣有問題如果row總數是奇數的話最後一筆會出事 所以要特別處理！！
with a as (
select id+1 id, student
from seat
where id%2 = 1
union all
select id-1, student
from seat
where id%2 = 0
), b as (
select *
from a
where id = 1
union all
select x.id, x.student
from a x, a y
where x.id = y.id+1
union all
select (select count(id) from a), a.student
from a
where id = (case when ((select count(id) from a) % 2) = 1 then (select max(id) from a) else 0 end)
)
select *
from b
order by id
ver.2
ver1把奇數的id與偶數的id分成兩個table 再用union all接起來
但其實只要針對id做case就好了…
with a as (
select count(*) total
from seat
)
select (case
when ((seat.id%2)=1) and seat.id != a.total then seat.id+1
when ((seat.id%2)=1) and seat.id = a.total then seat.id
else seat.id-1
end) id,
seat.student
from seat, a
order by id
627. Swap Salary
延續626的想法針對想要的欄位做case 這裡對sex做case就可以了
update salary
set sex = (case sex when 'm' then 'f' else 'm' end)