題目選自leetcode 上的題庫(kù)
可能不一定都是最優(yōu)解，答案僅供參考
每道題后面都應(yīng)相應(yīng)的難度等級(jí)，如果沒時(shí)間做的話 可以在leetcode 按出題頻率刷題
祝大家面試取得好的成績(jī)
175. 組合兩個(gè)表
難度簡(jiǎn)單
SQL架構(gòu)
表1: Person
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主鍵
表2: Address
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主鍵
編寫一個(gè) SQL 查詢，滿足條件：無(wú)論 person 是否有地址信息，都需要基于上述兩表提供 person 的以下信息：
FirstName, LastName, City, State
select FirstName,LastName,City,State
from Person p
left join Address a
on a.PersonId = p.PersonId
176. 第二高的薪水
難度簡(jiǎn)單
SQL架構(gòu)
編寫一個(gè) SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查詢應(yīng)該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應(yīng)返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
177. 第N高的薪水
難度中等
編寫一個(gè) SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 時(shí)，應(yīng)返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查詢應(yīng)返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
SELECT IFNULL(
(select salary
from(
select salary,
rank() over(order by salary desc) rk
from Employee
group by salary
)t1
where rk=N),NULL) SecondHighestSalary
);
END
178. 分?jǐn)?shù)排名
難度中等
SQL架構(gòu)
編寫一個(gè) SQL 查詢來(lái)實(shí)現(xiàn)分?jǐn)?shù)排名。
如果兩個(gè)分?jǐn)?shù)相同，則兩個(gè)分?jǐn)?shù)排名（Rank）相同。請(qǐng)注意，平分后的下一個(gè)名次應(yīng)該是下一個(gè)連續(xù)的整數(shù)值。換句話說(shuō)，名次之間不應(yīng)該有“間隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根據(jù)上述給定的 Scores 表，你的查詢應(yīng)該返回（按分?jǐn)?shù)從高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示：對(duì)于 MySQL 解決方案，如果要轉(zhuǎn)義用作列名的保留字，可以在關(guān)鍵字之前和之后使用撇號(hào)。例如 Rank
select Score,
dense_rank() over(order by Score desc) `rank`
from Scores
180. 連續(xù)出現(xiàn)的數(shù)字
難度中等
SQL架構(gòu)
編寫一個(gè) SQL 查詢，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，給定上面的 Logs 表， 1 是唯一連續(xù)出現(xiàn)至少三次的數(shù)字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
select distinct Num ConsecutiveNums
from
(
select
Num,
lead(Num,1,null) over(order by id) n2,
lead(Num,2,null) over(order by id) n3
from Logs
)t1
where Num = n2 and Num = n3
181. 超過(guò)經(jīng)理收入的員工
難度簡(jiǎn)單
SQL架構(gòu)
Employee 表包含所有員工，他們的經(jīng)理也屬于員工。每個(gè)員工都有一個(gè) Id，此外還有一列對(duì)應(yīng)員工的經(jīng)理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
給定 Employee 表，編寫一個(gè) SQL 查詢，該查詢可以獲取收入超過(guò)他們經(jīng)理的員工的姓名。在上面的表格中，Joe 是唯一一個(gè)收入超過(guò)他的經(jīng)理的員工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
select a.Name Employee
from Employee a
join Employee b
on a.ManagerId = b.id
where a.Salary>b.Salary
182. 查找重復(fù)的電子郵箱
難度簡(jiǎn)單
SQL架構(gòu)
編寫一個(gè) SQL 查詢，查找 Person 表中所有重復(fù)的電子郵箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根據(jù)以上輸入，你的查詢應(yīng)返回以下結(jié)果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
說(shuō)明：所有電子郵箱都是小寫字母。
select Email
from Person
group by Email
having count(*)>1
183. 從不訂購(gòu)的客戶
難度簡(jiǎn)單
SQL架構(gòu)
某網(wǎng)站包含兩個(gè)表，Customers 表和 Orders 表。編寫一個(gè) SQL 查詢，找出所有從不訂購(gòu)任何東西的客戶。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如給定上述表格，你的查詢應(yīng)返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
select c.Name Customers
from Customers c left join Orders o
on c.id = o.CustomerId
where o.id is null
184. 部門工資最高的員工
難度中等
SQL架構(gòu)
Employee 表包含所有員工信息，每個(gè)員工有其對(duì)應(yīng)的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個(gè) SQL 查詢，找出每個(gè)部門工資最高的員工。例如，根據(jù)上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
select Department,Employee,Salary
from (
select d.Name Department,e.Name Employee, e.Salary,
rank() over(partition by d.id order by Salary desc) rk
from Employee e join Department d
on e.DepartmentId=d.id
)tmp
where rk = 1
185. 部門工資前三高的所有員工
難度困難
SQL架構(gòu)
Employee 表包含所有員工信息，每個(gè)員工有其對(duì)應(yīng)的工號(hào) Id，姓名 Name，工資 Salary 和部門編號(hào) DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個(gè) SQL 查詢，找出每個(gè)部門獲得前三高工資的所有員工。例如，根據(jù)上述給定的表，查詢結(jié)果應(yīng)返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解釋：
IT 部門中，Max 獲得了最高的工資，Randy 和 Joe 都拿到了第二高的工資，Will 的工資排第三。銷售部門（Sales）只有兩名員工，Henry 的工資最高，Sam 的工資排第二。
select Department,Employee,Salary
from (
select d.Name Department,e.Name Employee, e.Salary,
dense_rank() over(partition by d.id order by Salary desc) rk
from Employee e join Department d
on e.DepartmentId=d.id
)tmp
where rk <=3
196. 刪除重復(fù)的電子郵箱
難度簡(jiǎn)單
編寫一個(gè) SQL 查詢，來(lái)刪除 Person 表中所有重復(fù)的電子郵箱，重復(fù)的郵箱里只保留 Id 最小 的那個(gè)。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是這個(gè)表的主鍵。
例如，在運(yùn)行你的查詢語(yǔ)句之后，上面的 Person 表應(yīng)返回以下幾行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示：
執(zhí)行 SQL 之后，輸出是整個(gè)
Person表。使用
delete語(yǔ)句。
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
注意是刪除 ，不是查詢
197. 上升的溫度
難度簡(jiǎn)單
SQL架構(gòu)
給定一個(gè) Weather 表，編寫一個(gè) SQL 查詢，來(lái)查找與之前（昨天的）日期相比溫度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如，根據(jù)上述給定的 Weather 表格，返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
select
Id
from
(
select Id,RecordDate,Temperature,
lag(RecordDate,1,9999-99-99) over (order by RecordDate) yd,
lag(Temperature,1,999) over(order by RecordDate ) yt
from Weather
)tmp
where Temperature >yt
and datediff(RecordDate,yd)=1
262. 行程和用戶
難度困難
SQL架構(gòu)
Trips 表中存所有出租車的行程信息。每段行程有唯一鍵 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外鍵。Status 是枚舉類型，枚舉成員為 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用戶。每個(gè)用戶有唯一鍵 Users_Id。Banned 表示這個(gè)用戶是否被禁止，Role 則是一個(gè)表示（‘client’, ‘driver’, ‘partner’）的枚舉類型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
寫一段 SQL 語(yǔ)句查出 2013年10月1日 至 2013年10月3日 期間非禁止用戶的取消率?；谏媳恚愕?SQL 語(yǔ)句應(yīng)返回如下結(jié)果，取消率（Cancellation Rate）保留兩位小數(shù)。
取消率的計(jì)算方式如下：(被司機(jī)或乘客取消的非禁止用戶生成的訂單數(shù)量) / (非禁止用戶生成的訂單總數(shù))
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
SELECT T.request_at AS `Day`,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS `Cancellation Rate`
FROM trips AS T
WHERE
T.Client_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
511. 游戲玩法分析 I
難度簡(jiǎn)單
SQL架構(gòu)
活動(dòng)表 Activity：
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+--------------+---------+
表的主鍵是 (player_id, event_date)。
這張表展示了一些游戲玩家在游戲平臺(tái)上的行為活動(dòng)。
每行數(shù)據(jù)記錄了一名玩家在退出平臺(tái)之前，當(dāng)天使用同一臺(tái)設(shè)備登錄平臺(tái)后打開的游戲的數(shù)目（可能是 0 個(gè)）。
寫一條 SQL 查詢語(yǔ)句獲取每位玩家 第一次登陸平臺(tái)的日期。
查詢結(jié)果的格式如下所示：
Activity 表：
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result 表：
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+-----------+-------------+
select player_id ,event_date first_login
from (
select player_id ,event_date,
rank() over(partition by player_id order by event_date) rk
from Activity
) tmp
where rk = 1
2.最優(yōu) （選最小日期）
select player_id ,min(event_date) first_login
from Activity
group by player_id
