小李飛刀:SQL題目刷起來!
隨便說說
好幾天沒有認真刷題了,這兩天猛刷了一把SQL題目。
然後用hexo搭建了自己的BLOG ,還在摸索中,後續漸漸的就會兩邊都同步文章。
SQL題集
leetcode上對於資料庫是有單獨的19題的,我現在的進度是8/19,刷的還是有點慢,而且很多地方效率不高,還得做n刷處理。
畢竟後續如果考慮到要說資料分析的話,取數上的效率也得保證。
第一題
175. 組合兩個表
難度:簡單
表1: Person
列名 | 型別 |
---|---|
PersonId | int |
FirstName | varchar |
LastName | varchar |
PersonId 是上表主鍵
表2: Address
列名 | 型別 |
---|---|
AddressId | int |
PersonId | int |
City | varchar |
State | varchar |
AddressId 是上表主鍵
編寫一個 SQL 查詢,滿足條件:無論 person 是否有地址資訊,都需要基於上述兩表提供 person 的以下資訊:
我的題解:
SELECT Person.FirstName,Person.LastName,Address.City,Address.State From Person Left Join Address ON Person.PersonId = Address.PersonId
解題思路:
因為無論address可能為空,所以用left join的方式,加入Address表。
其他:
很久沒有用過left join,有些概念有點忘記,順便來複習下知識點。
在left join
之前的左表是會被完全返回的,哪怕left join的右表沒有對應的資料。
select * from table_1 left join table_2
這裡的話會返回所有table_1
的行。
sql的left join 、right join 、inner join之間的區別:
-left join(左聯接)
返回包括左表中的所有記錄和右表中聯結欄位相等的記錄
-right join(右聯接)
返回包括右表中的所有記錄和左表中聯結欄位相等的記錄
-inner join(等值連線)
只返回兩個表中聯結欄位相等的行
第二題
176. 第二高的薪水
難度:簡單
編寫一個 SQL 查詢,獲取 Employee 表中第二高的薪水(Salary) 。 | |
---|---|
Id | Salary |
1 | 100 |
2 | 200 |
3 | 300 |
例如上述 Employee 表,SQL查詢應該返回 200 作為第二高的薪水。如果不存在第二高的薪水,那麼查詢應返回 null。
SecondHighestSalary |
---|
200 |
我的題解:
SELECT MAX(Salary) AS SecondHighestSalaryFROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee)
解題思路:
使用max()
來獲取兩次最大值,因為是同一張表,小於最大值的“最大值”就是第二大的值了。
其他:
一般主要查詢最大值,這題查詢的是第二大的值。
主要是思路上要調整下,一般程式語言上會做排序。
SQL裡面也可以考慮用排序試下,如果要取第二條資料的話,就得先取前兩條資料,再倒序取第一條。
第三題
181. 超過經理收入的員工
難度:簡單
Employee 表包含所有員工,他們的經理也屬於員工。每個員工都有一個 Id,此外還有一列對應員工的經理的 Id。 | |||
---|---|---|---|
Id | Name | Salary | ManagerId |
1 | Joe | 70000 | 3 |
2 | Henry | 80000 | 4 |
3 | Sam | 60000 | NULL |
4 | Max | 90000 | NULL |
給定 Employee 表,編寫一個 SQL 查詢,該查詢可以獲取收入超過他們經理的員工的姓名。在上面的表格中,Joe 是唯一一個收入超過他的經理的員工。
Employee |
---|
Joe |
我的題解:
SELECT p1.Name AS Employee FROM Employee p1,Employee p2 WHERE p1.ManagerId = p2.Id AND p1.Salary > p2.Salary
解題思路:
查詢兩次同一張表,主條件為匹配經理Id和使用者Id,再做比對大小。
其他:
對於同一張表查詢兩次,其實應該驗證下效率到底如何,檢查下是否有更快的查詢方案。
第四題
182. 查詢重複的電子郵箱
難度:簡單
編寫一個 SQL 查詢,查詢 Person 表中所有重複的電子郵箱。
示例: | |
---|---|
Id | |
1 | [email protected] |
2 | [email protected] |
3 | [email protected] |
根據以上輸入,你的查詢應返回以下結果:
[email protected] |
說明:所有電子郵箱都是小寫字母。
我的題解:
SELECT distinct(p1.Email) from Person p1,Person p2 where p1.Email = p2.Email AND p1.Id != p2.Id
解題思路:
還是查詢同一張表兩次,然後使用distinct,只輸出單個結果。
其他:
distinct
用於返回唯一不同的值。
有distinct的欄位必須放在開頭。
第五題
183. 從不訂購的客戶
難度:簡單
某網站包含兩個表,Customers 表和 Orders 表。編寫一個 SQL 查詢,找出所有從不訂購任何東西的客戶。
Id | Name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
Orders 表: | |
---|---|
Id | CustomerId |
1 | 3 |
2 | 1 |
例如給定上述表格,你的查詢應返回:
Customers |
---|
Henry |
Max |
我的題解:
SELECT c.name AS Customers FROM Customers c WHERE c.Id Not in(SELECT CustomerId FROM Orders)
解題思路:
取出Order表的資料,然後和Customers的Id做校驗。
其他:
如果不是用取出Customers的ID來做比較的,就是Id!=CusomerId,而是查詢兩張表直接輸出結果的話,會把每次的不對應的結果都輸出。因為等於兩張表都被完整比對過一次。
第六題
184. 部門工資最高的員工
難度:中等
Employee 表包含所有員工資訊,每個員工有其對應的 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 |
編寫一個 SQL 查詢,找出每個部門工資最高的員工。例如,根據上述給定的表格,Max 在 IT 部門有最高工資,Henry 在 Sales 部門有最高工資。
Department | Employee | Salary |
---|---|---|
IT | Max | 90000 |
Sales | Henry | 80000 |
我的題解:
select d.Name as Department,e.Name as Employee,e.Salary from Department d,Employee e where e.DepartmentId = d.ID and e.Salary = (select max(Salary) from Employee where d.id = DepartmentId)
解題思路:
這題參考了其他人的思路,後續需要自己再寫一次。
其實是轉了兩次彎,第一次是根據部門Id查詢出每個部門最高的薪水,再根據這個薪水找到對應的人。
其他:
第七題
196. 刪除重複的電子郵箱
難度:簡單
編寫一個 SQL 查詢,來刪除 Person 表中所有重複的電子郵箱,重複的郵箱裡只保留 Id 最小 的那個。 | |
---|---|
Id | |
1 | [email protected] |
2 | [email protected] |
3 | [email protected] |
Id 是這個表的主鍵。
例如,在執行你的查詢語句之後,上面的 Person 表應返回以下幾行: | |
---|---|
Id | |
1 | [email protected] |
2 | [email protected] |
我的題解:
DELETE p1 FROM Person p1,Person p2 WHERE p1.Email = p2.Email and p1.Id > p2.Id
解題思路:
這題一開始也有點被繞住了,後面漸漸做多了兩次查詢同步一張表就還好,
核心思路就是查詢相同的值,且Id不同,我們delete的是Id較大的那一行。
其他:
Null.
第八題
596. 超過5名學生的課
難度:簡單
有一個courses 表 ,有: student (學生) 和 class (課程)。
請列出所有超過或等於5名學生的課。
例如,表: | |
---|---|
student | class |
A | Math |
B | English |
C | Math |
D | Biology |
E | Math |
F | Computer |
G | Math |
H | Math |
I | Math |
應該輸出: |
---|
class |
Math |
我的題解:
select class From course group by class having count(class)>=5
解題思路:
對課程進行分組,分組後記數大於等於5的就取出數值。
其他
Null
學生在每個課中不應被重複計算。
我的題解:
SELECT class FROM (select distinct * from courses) as new GROUP BY class HAVING count(*) >= 5
解題思路:
其他: