- sql語句：表【學生|科目|成績】查詢每個學生成績最好的科目和成績
CREATE TABLE `scores` (
`name` text,
`subject` text,
`score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
select * from scores
where (name,score) in
(select name,Max(score) from scores group by name);
參考：https://www.zhihu.com/question/302413652
175. 組合兩個表
SQL架構(gòu)
表1: Person
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主鍵
表2: Address
+-------------+---------+
| 列名 | 類型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主鍵
編寫一個 SQL 查詢，滿足條件：無論 person 是否有地址信息，都需要基于上述兩表提供 person 的以下信息：
FirstName, LastName, City, State
*/
/*
方法：使用 outer join
算法
因為表 Address 中的 personId 是表 Person 的外關(guān)鍵字，所以我們可以連接這兩個表來獲取一個人的地址信息。
考慮到可能不是每個人都有地址信息，我們應該使用 outer join 而不是默認的 inner join。
*/
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId
;
/*
left join 和 inner join的區(qū)別？
假設(shè)你要join兩個沒有重復列的表，這是最常見的情況：
inner join A 和 B 獲得的是A和B的交集(intersect),即韋恩圖(venn diagram) 相交的部分.
outer join A和B獲得的是A和B的并集(union), 即韋恩圖(venn diagram)的所有部分.
示例
假定有兩張表，每張表只有一列，列數(shù)據(jù)如下：
A B
- -
1 3
2 4
3 5
4 6
注意(1,2)是A表獨有的，(3,4) 兩張共有， (5,6)是B獨有的。
Inner join
使用等號進行inner join以獲得兩表的交集，即共有的行。
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
left outer join 除了獲得B表中符合條件的列外，還將獲得A表所有的列。
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Full outer join
full outer join 得到A和B的交集,即A和B中所有的行.。如果A中的行在B中沒有對應的部分,B的部分將是 null, 反之亦然。
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
176. 第二高的薪水
SQL架構(gòu)
編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，SQL查詢應該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應返回 null。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
select (select distinct Salary from `Employee` order by Salary desc limit 1 offset 1) as SecondHighestSalary
177. 第N高的薪水
編寫一個 SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 時，應返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查詢應返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
*/
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
排名是數(shù)據(jù)庫中的一個經(jīng)典題目，實際上又根據(jù)排名的具體細節(jié)可分為3種場景：
連續(xù)排名，例如薪水3000、2000、2000、1000排名結(jié)果為1-2-3-4，體現(xiàn)同薪不同名，排名類似于編號
同薪同名但總排名不連續(xù)，例如同樣的薪水分布，排名結(jié)果為1-2-2-4
同薪同名且總排名連續(xù)，同樣的薪水排名結(jié)果為1-2-2-3
不同的應用場景可能需要不同的排名結(jié)果，也意味著不同的查詢策略。本題的目標是實現(xiàn)第三種排名方式下的第N個結(jié)果，且是全局排名，不存在分組的問題，實際上還要相對簡單一些。
值得一提的是：在Oracle等數(shù)據(jù)庫中有窗口函數(shù)，可非常容易實現(xiàn)這些需求，而MySQL直到8.0版本也引入相關(guān)函數(shù)。最新OJ環(huán)境已更新至8.0版本，可直接使用窗口函數(shù)。
為此，本文提出以下幾種解決思路，僅供參考。
如果有意可關(guān)注文末個人公眾號，查看一篇更為詳盡的分組排名問題。
思路1：單表查詢
由于本題不存在分組排序，只需返回全局第N高的一個，所以自然想到的想法是用order by排序加limit限制得到。需要注意兩個細節(jié)：
同薪同名且不跳級的問題，解決辦法是用group by按薪水分組后再order by
排名第N高意味著要跳過N-1個薪水，由于無法直接用limit N-1，所以需先在函數(shù)開頭處理N為N=N-1。
注：這里不能直接用limit N-1是因為limit和offset字段后面只接受正整數(shù)（意味著0、負數(shù)、小數(shù)都不行）或者單一變量（意味著不能用表達式），也就是說想取一條，limit 2-1、limit 1.1這類的寫法都是報錯的。
注：這種解法形式最為簡潔直觀，但僅適用于查詢?nèi)峙琶麊栴}，如果要求各分組的每個第N名，則該方法不適用；而且也不能處理存在重復值的情況。
代碼1
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
思路2：子查詢
排名第N的薪水意味著該表中存在N-1個比其更高的薪水
注意這里的N-1個更高的薪水是指去重后的N-1個，實際對應人數(shù)可能不止N-1個
最后返回的薪水也應該去重，因為可能不止一個薪水排名第N
由于對于每個薪水的where條件都要執(zhí)行一遍子查詢，注定其效率低下
代碼2
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
(SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
);
END
思路3：自連接
一般來說，能用子查詢解決的問題也能用連接解決。具體到本題：
兩表自連接，連接條件設(shè)定為表1的salary小于表2的salary
以表1的salary分組，統(tǒng)計表1中每個salary分組后對應表2中salary唯一值個數(shù)，即去重
限定步驟2中having 計數(shù)個數(shù)為N-1，即實現(xiàn)了該分組中表1salary排名為第N個
考慮N=1的特殊情形(特殊是因為N-1=0，計數(shù)要求為0)，此時不存在滿足條件的記錄數(shù)，但仍需返回結(jié)果，所以連接用left join
如果僅查詢薪水這一項值，那么不用left join當然也是可以的，只需把連接條件放寬至小于等于、同時查詢個數(shù)設(shè)置為N即可。因為連接條件含等號，所以一定不為空，用join即可。
注：個人認為無需考慮N<=0的情形，畢竟無實際意義。
代碼3
mysqlmysql
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路4：笛卡爾積
當然，可以很容易將思路2中的代碼改為笛卡爾積連接形式，其執(zhí)行過程實際上一致的，甚至MySQL執(zhí)行時可能會優(yōu)化成相同的查詢語句。
代碼4
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1, employee e2
WHERE
e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路5：自定義變量
以上方法2-4中均存在兩表關(guān)聯(lián)的問題，表中記錄數(shù)少時尚可接受，當記錄數(shù)量較大且無法建立合適索引時，實測速度會比較慢，用算法復雜度來形容大概是O(n^2)量級（實際還與索引有關(guān)）。那么，用下面的自定義變量的方法可實現(xiàn)O(2*n)量級，速度會快得多，且與索引無關(guān)。
自定義變量實現(xiàn)按薪水降序后的數(shù)據(jù)排名，同薪同名不跳級，即3000、2000、2000、1000排名后為1、2、2、3；
對帶有排名信息的臨時表二次篩選，得到排名為N的薪水；
因為薪水排名為N的記錄可能不止1個，用distinct去重
代碼5
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N
);
END
思路6：窗口函數(shù)
實際上，在mysql8.0中有相關(guān)的內(nèi)置函數(shù)，而且考慮了各種排名問題：
row_number(): 同薪不同名，相當于行號，例如3000、2000、2000、1000排名后為1、2、3、4
rank(): 同薪同名，有跳級，例如3000、2000、2000、1000排名后為1、2、2、4
dense_rank(): 同薪同名，無跳級，例如3000、2000、2000、1000排名后為1、2、2、3
ntile(): 分桶排名，即首先按桶的個數(shù)分出第一二三桶，然后各桶內(nèi)從1排名，實際不是很常用
顯然，本題是要用第三個函數(shù)。
另外這三個函數(shù)必須要要與其搭檔over()配套使用，over()中的參數(shù)常見的有兩個，分別是
partition by，按某字段切分
order by，與常規(guī)order by用法一致，也區(qū)分ASC(默認)和DESC，因為排名總得有個依據(jù)
注：下面代碼僅在mysql8.0以上版本可用，最新OJ已支持。
代碼6
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
至此，可以總結(jié)MySQL查詢的一般性思路是：
能用單表優(yōu)先用單表，即便是需要用group by、order by、limit等，效率一般也比多表高
不能用單表時優(yōu)先用連接，連接是SQL中非常強大的用法，小表驅(qū)動大表+建立合適索引+合理運用連接條件，基本上連接可以解決絕大部分問題。但join級數(shù)不宜過多，畢竟是一個接近指數(shù)級增長的關(guān)聯(lián)效果
能不用子查詢、笛卡爾積盡量不用，雖然很多情況下MySQL優(yōu)化器會將其優(yōu)化成連接方式的執(zhí)行過程，但效率仍然難以保證
自定義變量在復雜SQL實現(xiàn)中會很有用，例如LeetCode中困難級別的數(shù)據(jù)庫題目很多都需要借助自定義變量實現(xiàn)
如果MySQL版本允許，某些帶聚合功能的查詢需求應用窗口函數(shù)是一個最優(yōu)選擇。除了經(jīng)典的獲取3種排名信息，還有聚合函數(shù)、向前向后取值、百分位等，具體可參考官方指南。以下是官方給出的幾個窗口函數(shù)的介紹：
最后的最后再補充一點，本題將查詢語句封裝成一個自定義函數(shù)并給出了模板，實際上是降低了對函數(shù)語法的書寫要求和難度，而且提供的函數(shù)寫法也較為精簡。然而，自定義函數(shù)更一般化和常用的寫法應該是分三步：
定義變量接收返回值
執(zhí)行查詢條件，并賦值給相應變量
返回結(jié)果
例如以解法5為例，如下寫法可能更適合函數(shù)初學者理解和掌握：
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
# i 定義變量接收返回值
DECLARE ans INT DEFAULT NULL;
# ii 執(zhí)行查詢語句，并賦值給相應變量
SELECT
DISTINCT salary INTO ans
FROM
(SELECT
salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
FROM
employee, (SELECT @r:=0, @p:=NULL)init
ORDER BY
salary DESC) tmp
WHERE rnk = N;
# iii 返回查詢結(jié)果，注意函數(shù)名中是 returns，而函數(shù)體中是 return
RETURN ans;
END
*/
178. 分數(shù)排名
SQL架構(gòu)
編寫一個 SQL 查詢來實現(xiàn)分數(shù)排名。
如果兩個分數(shù)相同，則兩個分數(shù)排名（Rank）相同。請注意，平分后的下一個名次應該是下一個連續(xù)的整數(shù)值。換句話說，名次之間不應該有“間隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根據(jù)上述給定的 Scores 表，你的查詢應該返回（按分數(shù)從高到低排列）：
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示：對于 MySQL 解決方案，如果要轉(zhuǎn)義用作列名的保留字，可以在關(guān)鍵字之前和之后使用撇號。例如 `Rank`
*/
/*
最后的結(jié)果包含兩個部分，第一部分是降序排列的分數(shù)，第二部分是每個分數(shù)對應的排名。
第一部分不難寫：
select a.Score as Score
from Scores a
order by a.Score DESC
比較難的是第二部分。假設(shè)現(xiàn)在給你一個分數(shù)X，如何算出它的排名Rank呢？
我們可以先提取出大于等于X的所有分數(shù)集合H，將H去重后的元素個數(shù)就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一個元素，個數(shù)為1，因此你的Rank為1。
先提取集合H：
select b.Score from Scores b where b.Score >= X;
我們要的是集合H去重之后的元素個數(shù)，因此升級為：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
而從結(jié)果的角度來看，第二部分的Rank是對應第一部分的分數(shù)來的，所以這里的X就是上面的a.Score，把兩部分結(jié)合在一起為：
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as `Rank`
from Scores a
order by a.Score desc
/*
180. 連續(xù)出現(xiàn)的數(shù)字
SQL架構(gòu)
編寫一個 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 |
+-----------------+
*/
/*
方法：用 DISTINCT 和 WHERE 語句
算法
連續(xù)出現(xiàn)的意味著相同數(shù)字的 Id 是連著的，由于這題問的是至少連續(xù)出現(xiàn) 3 次，我們使用 Logs 并檢查是否有 3 個連續(xù)的相同數(shù)字。
SELECT *
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
Id Num Id Num Id Num
1 1 2 1 3 1
注意：前兩列來自 l1 ，接下來兩列來自 l2 ，最后兩列來自 l3 。
然后我們從上表中選擇任意的 Num 獲得想要的答案。同時我們需要添加關(guān)鍵字 DISTINCT ，因為如果一個數(shù)字連續(xù)出現(xiàn)超過 3 次，會返回重復元素。
MySQL
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
*/
select distinct l1.Num as ConsecutiveNums
from
Logs l1,
Logs l2,
Logs l3
where
l1.Id = l2.Id - 1
and l2.Id = l3.Id - 1
and l1.Num = l2.Num
and l2.Num = l3.Num;
/*
181. 超過經(jīng)理收入的員工
SQL架構(gòu)
Employee 表包含所有員工，他們的經(jīng)理也屬于員工。每個員工都有一個 Id，此外還有一列對應員工的經(jīng)理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
給定 Employee 表，編寫一個 SQL 查詢，該查詢可以獲取收入超過他們經(jīng)理的員工的姓名。
在上面的表格中，Joe 是唯一一個收入超過他的經(jīng)理的員工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
*/
select a.Name as Employee from Employee as a join Employee as b on a.ManagerId = b.Id;
/*
方法 1：使用 WHERE 語句
算法
如下面表格所示，表格里存有每個雇員經(jīng)理的信息，我們也許需要從這個表里獲取兩次信息。
SELECT *
FROM Employee AS a, Employee AS b
;
注意：關(guān)鍵詞 'AS' 是可選的
Id Name Salary ManagerId Id Name Salary ManagerId
1 Joe 70000 3 1 Joe 70000 3
2 Henry 80000 4 1 Joe 70000 3
3 Sam 60000 1 Joe 70000 3
4 Max 90000 1 Joe 70000 3
1 Joe 70000 3 2 Henry 80000 4
2 Henry 80000 4 2 Henry 80000 4
3 Sam 60000 2 Henry 80000 4
4 Max 90000 2 Henry 80000 4
1 Joe 70000 3 3 Sam 60000
2 Henry 80000 4 3 Sam 60000
3 Sam 60000 3 Sam 60000
4 Max 90000 3 Sam 60000
1 Joe 70000 3 4 Max 90000
2 Henry 80000 4 4 Max 90000
3 Sam 60000 4 Max 90000
4 Max 90000 4 Max 90000
前 3 列來自表格 a ，后 3 列來自表格 b
從兩個表里使用 Select 語句可能會導致產(chǎn)生 笛卡爾乘積 。在這種情況下，輸出會產(chǎn)生 4*4=16 個記錄。然而我們只對雇員工資高于經(jīng)理的人感興趣。所以我們應該用 WHERE 語句加 2 個判斷條件。
SELECT
*
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
Id Name Salary ManagerId Id Name Salary ManagerId
1 Joe 70000 3 3 Sam 60000
由于我們只需要輸出雇員的名字，所以我們修改一下上面的代碼，得到最終解法：
MySQL
SELECT
a.Name AS 'Employee'
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
方法 2：使用 JOIN 語句
算法
實際上， JOIN 是一個更常用也更有效的將表連起來的辦法，我們使用 ON 來指明條件。
SELECT
a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary
;
*/
/*
182. 查找重復的電子郵箱
SQL架構(gòu)
編寫一個 SQL 查詢，查找 Person 表中所有重復的電子郵箱。
示例：
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
根據(jù)以上輸入，你的查詢應返回以下結(jié)果：
+---------+
| Email |
+---------+
| a@b.com |
+---------+
說明：所有電子郵箱都是小寫字母。
*/
select Email from
(select Email,count(Email) as num from Person group by Email) as statistics
where num > 1
select Email from Person group by Email having count(*) > 1
/*
方法一：使用 GROUP BY 和臨時表
算法
重復的電子郵箱存在多次。要計算每封電子郵件的存在次數(shù)，我們可以使用以下代碼。
MySQL
select Email, count(Email) as num
from Person
group by Email;
| Email | num |
|---------|-----|
| a@b.com | 2 |
| c@d.com | 1 |
以此作為臨時表，我們可以得到下面的解決方案。
MySQL
select Email from
(
select Email, count(Email) as num
from Person
group by Email
) as statistic
where num > 1
;
方法二：使用 GROUP BY 和 HAVING 條件
向 GROUP BY 添加條件的一種更常用的方法是使用 HAVING 子句，該子句更為簡單高效。
所以我們可以將上面的解決方案重寫為：
MySQL
select Email
from Person
group by Email
having count(Email) > 1;
*/
/*
183. 從不訂購的客戶
SQL架構(gòu)
某網(wǎng)站包含兩個表，Customers 表和 Orders 表。編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如給定上述表格，你的查詢應返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
*/
select customers.name as 'Customers' from customers
where customers.id not in (
select customerid from orders
)
/*
方法：使用子查詢和 NOT IN 子句
算法
如果我們有一份曾經(jīng)訂購過的客戶名單，就很容易知道誰從未訂購過。
我們可以使用下面的代碼來獲得這樣的列表。
select customerid from orders;
然后，我們可以使用 NOT IN 查詢不在此列表中的客戶。
MySQL
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
*/
/*
184. 部門工資最高的員工
SQL架構(gòu)
Employee 表包含所有員工信息，每個員工有其對應的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部門的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
編寫一個 SQL 查詢，找出每個部門工資最高的員工。對于上述表，您的 SQL 查詢應返回以下行（行的順序無關(guān)緊要）。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解釋：
Max 和 Jim 在 IT 部門的工資都是最高的，Henry 在銷售部的工資最高。
*/
select department.name as 'Department',
employee.name as 'Employee',
salary
from department join employee on employee.departmentid = department.id
where (employee.departmentid,salary) in
(select departmentid ,MAX(salary) from employee group by departmentid)
/*
方法：使用 JOIN 和 IN 語句
算法
因為 Employee 表包含 Salary 和 DepartmentId 字段，我們可以以此在部門內(nèi)查詢最高工資。
SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId;
注意：有可能有多個員工同時擁有最高工資，所以最好在這個查詢中不包含雇員名字的信息。
| DepartmentId | MAX(Salary) |
|--------------|-------------|
| 1 | 90000 |
| 2 | 80000 |
然后，我們可以把表 Employee 和 Department 連接，再在這張臨時表里用 IN 語句查詢部門名字和工資的關(guān)系。
MySQL
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
| Department | Employee | Salary |
|------------|----------|--------|
| Sales | Henry | 80000 |
| IT | Max | 90000 |
*/
/*
185. 部門工資前三高的所有員工
SQL架構(gòu)
Employee 表包含所有員工信息，每個員工有其對應的工號 Id，姓名 Name，工資 Salary 和部門編號 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 |
+----+----------+
編寫一個 SQL 查詢，找出每個部門獲得前三高工資的所有員工。例如，根據(jù)上述給定的表，查詢結(jié)果應返回：
+------------+----------+--------+
| 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 的工資排第二。
*/
/*
方法：使用 JOIN 和子查詢
算法
公司里前 3 高的薪水意味著有不超過 3 個工資比這些值大。
select e1.Name as 'Employee', e1.Salary
from Employee e1
where 3 >
(
select count(distinct e2.Salary)
from Employee e2
where e2.Salary > e1.Salary
)
;
在這個代碼里，我們統(tǒng)計了有多少人的工資比 e1.Salary 高，所以樣例的輸出應該如下所示。
| Employee | Salary |
|----------|--------|
| Henry | 80000 |
| Max | 90000 |
| Randy | 85000 |
然后，我們需要把表 Employee 和表 Department 連接來獲得部門信息。
MySQL
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
| Department | Employee | Salary |
|------------|----------|--------|
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
| IT | Max | 90000 |
| IT | Randy | 85000 |
*/
select d.Name as 'Department',e.Name as 'Employee',e.Salary from employee as e join department as d
on e.DepartmentId = d.id
where 3 > ( select count(distinct e2.salary) from employee as e2 where e2.salary > e.salary
and e2.DepartmentId = e.DepartmentId)
/*
196. 刪除重復的電子郵箱
編寫一個 SQL 查詢，來刪除 Person 表中所有重復的電子郵箱，重復的郵箱里只保留 Id 最小 的那個。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是這個表的主鍵。
例如，在運行你的查詢語句之后，上面的 Person 表應返回以下幾行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示：
執(zhí)行 SQL 之后，輸出是整個 Person 表。
使用 delete 語句。
*/
/*
方法：使用 DELETE 和 WHERE 子句
算法
我們可以使用以下代碼，將此表與它自身在電子郵箱列中連接起來。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;
然后我們需要找到其他記錄中具有相同電子郵件地址的更大 ID。所以我們可以像這樣給 WHERE 子句添加一個新的條件。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
因為我們已經(jīng)得到了要刪除的記錄，所以我們最終可以將該語句更改為 DELETE。
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
*/
/*
方法：使用 DELETE 和 WHERE 子句
算法
我們可以使用以下代碼，將此表與它自身在電子郵箱列中連接起來。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email
;
然后我們需要找到其他記錄中具有相同電子郵件地址的更大 ID。所以我們可以像這樣給 WHERE 子句添加一個新的條件。
MySQL
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
因為我們已經(jīng)得到了要刪除的記錄，所以我們最終可以將該語句更改為 DELETE。
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
官方sql是下面這樣的??
MySQL
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
當然這個sql是很ok的，簡潔清晰，且用了自連接的方式。
有慢查詢優(yōu)化經(jīng)驗的同學會清楚，在實際生產(chǎn)中，面對千萬上億級別的數(shù)據(jù)，連接的效率往往最高，因為用到索引的概率較高。
因此，建議學習使用官方的題解，但是有兩點，可能需要再解釋下：
1、DELETE p1
在DELETE官方文檔中，給出了這一用法，比如下面這個DELETE語句??
DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
這種DELETE方式很陌生，竟然和SELETE的寫法類似。它涉及到t1和t2兩張表，DELETE t1表示要刪除t1的一些記錄，具體刪哪些，就看WHERE條件，滿足就刪；
這里刪的是t1表中，跟t2匹配不上的那些記錄。
所以，官方sql中，DELETE p1就表示從p1表中刪除滿足WHERE條件的記錄。
2、p1.Id > p2.Id
繼續(xù)之前，先簡單看一下表的連接過程，這個搞懂了，理解WHERE條件就簡單了??
a. 從驅(qū)動表（左表）取出N條記錄；
b. 拿著這N條記錄，依次到被驅(qū)動表（右表）查找滿足WHERE條件的記錄；
所以，官方sql的過程就是??
先把Person表搬過來( ????? )
a. 從表p1取出3條記錄；
b. 拿著第1條記錄去表p2查找滿足WHERE的記錄，代入該條件p1.Email = p2.Email AND p1.Id > p2.Id后，發(fā)現(xiàn)沒有滿足的，所以不用刪掉記錄1；
c. 記錄2同理；
d. 拿著第3條記錄去表p2查找滿足WHERE的記錄，發(fā)現(xiàn)有一條記錄滿足，所以要從p1刪掉記錄3；
e. 3條記錄遍歷完，刪掉了1條記錄，這個DELETE也就結(jié)束了。
*/
delete p1 from Person as p1,Person p2 where p1.Email = p2.Email and p1.Id > p2.Id
/*
197. 上升的溫度
SQL架構(gòu)
表 Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是這個表的主鍵
該表包含特定日期的溫度信息
編寫一個 SQL 查詢，來查找與之前（昨天的）日期相比溫度更高的所有日期的 id 。
返回結(jié)果 不要求順序 。
查詢結(jié)果格式如下例：
Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Result table:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
2015-01-02 的溫度比前一天高（10 -> 25）
2015-01-04 的溫度比前一天高（30 -> 20）
*/
/*
方法：使用 JOIN 和 DATEDIFF() 子句
算法
MySQL 使用 DATEDIFF 來比較兩個日期類型的值。
因此，我們可以通過將 weather 與自身相結(jié)合，并使用 DATEDIFF() 函數(shù)。
MySQL
SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.date, w.date) = 1
AND weather.Temperature > w.Temperature
;
*/
select w2.id as Id from Weather w1 , Weather w2 where DATEDIFF(w2.recordDate,w1.recordDate) = 1 and w2.Temperature > w1.Temperature
/*
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 表存所有用戶。每個用戶有唯一鍵 Users_Id。Banned 表示這個用戶是否被禁止，
Role 則是一個表示（‘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 語句查出 2013年10月1日 至 2013年10月3日 期間非禁止用戶的取消率。
基于上表，你的 SQL 語句應返回如下結(jié)果，取消率（Cancellation Rate）保留兩位小數(shù)。
取消率的計算方式如下：(被司機或乘客取消的非禁止用戶生成的訂單數(shù)量) / (非禁止用戶生成的訂單總數(shù))
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
致謝:
非常感謝 @cak1erlizhou 詳細的提供了這道題和相應的測試用例。
*/
/*
統(tǒng)計每天非禁止用戶的取消率，需要知道非禁止用戶有哪些，總行程數(shù)，取消的行程數(shù)。
解法一
首先確定被禁止用戶的行程記錄，再剔除這些行程記錄。
行程表中，字段 client_id 和 driver_id，都與用戶表中的 users_id 關(guān)聯(lián)。
因此只要 client_id 和 driver_id 中有一個被禁止了，此條行程記錄要被剔除。
先說一種錯誤的找出沒被禁止用戶行程記錄的方法。此方法很有迷惑性。
思路：
if (client_id = users_id 或 driver_id = users_id) 且 users_id沒有被禁止
{
此條記錄沒被禁止。
}
SQL 代碼
SELECT *
FROM Trips AS T JOIN Users AS U
ON (T.client_id = U.users_id OR T.driver_id = U.users_id ) AND U.banned ='No'
乍一看，思路是對。其實是錯誤的。因為，我們不知覺得肯定了一個假設(shè)—— client_id 與 driver_id 是相同的。
只有當兩者相同時，才能用此條件排除被禁止用戶的行程記錄。
錯誤的結(jié)果：
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
| Id | Client_Id | Driver_Id | City_Id | STATUS | Request_at | Users_Id | Banned | Role |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 | 1 | No | client |
| 1 | 1 | 10 | 1 | completed | 2013-10-01 | 10 | No | driver |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | 11 | No | driver |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 | 3 | No | client |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 | 12 | No | driver |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | 4 | No | client |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | 13 | No | driver |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 | 1 | No | client |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 | 10 | No | driver |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 | 11 | No | driver |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 | 3 | No | client |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 | 12 | No | driver |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 | 12 | No | driver |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 | 3 | No | client |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 | 10 | No | driver |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | 4 | No | client |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | 13 | No | driver |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
結(jié)果中，被禁止的 users_id = 2，其行程記錄沒被剔除掉。
明顯， client_id 與 driver_id 不一定相同 。
正確的做法是對 client_id 和 driver_id 各自關(guān)聯(lián)的 users_id，同時檢測是否被禁止。
if (client_id = users_id_1 且 users_id_1沒被禁止 并且 client_id = users_id_2 且 users_id_2沒被禁止){
此條記錄沒被禁止。
}
SQL 代碼：
SELECT *
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
在此基礎(chǔ)上，按日期分組，統(tǒng)計每組的 總行程數(shù)，取消的行程數(shù) 。
每組的總行程數(shù)：COUNT(T.STATUS)。
每組的取消的行程數(shù)：
SUM(
IF(T.STATUS = 'completed',0,1)
)
取消率 = 每組的取消的行程數(shù) / 每組的總行程數(shù)
完整邏輯為:
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
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
其中 SUM 求和函數(shù)，COUNT 計數(shù)函數(shù)，ROUND 四舍五入函數(shù)。
解法二
思路與解法一相同。而采用不同的方法排除掉被禁止用戶的行程記錄。想到排除，就聯(lián)想到集合差。
client_id 和 driver_id 的全部為集合 U。被禁止的 users_id 集合為 A。
U 減去 A 的結(jié)果為沒被禁止的用戶。
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
好了，先演示一個錯誤的解法：
行程表連接表 A，排除掉被被禁止的行程。
SELECT *
FROM trips AS T,
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id)
剩下的邏輯與解法一后部分相同，完善后的邏輯為：
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,
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id) AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
很可惜，當表 A 為空時，此方法的結(jié)果是空表。但是表 A 為空，可能是有用戶但是沒有被禁止的用戶。因此方法是錯誤的。
正確的解法是：行程表 left join 表 A 兩次，A.users_id 都為 NULL 的行都是沒被排除的行。
SELECT *
FROM trips AS T LEFT JOIN
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL
補上其它部分的邏輯為：
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 LEFT JOIN
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
解法三
與解法二思路相同。找出被禁止的用戶后，不再連接行程表和用戶表，直接從行程表中排除掉被被禁止用戶的行程記錄。
被禁止的用戶用子查詢：
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
)
行程表中 client_id 和 driver_id 都在此子查詢結(jié)果中的行要剔除掉。
SELECT *
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'
)
補上其它部分：
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
*/
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
join Users as U1 on (T.client_id = U1.users_id and U1.banned = 'NO')
join Users as U2 on (T.driver_id = U2.users_id and U2.banned = 'NO')
where T.request_at between '2013-10-01' AND '2013-10-03'
group by T.request_at
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
left join
( select users_id from users where banned = 'Yes') as A on (T.client_id = A.users_id)
left join
(select users_id from users where banned = 'Yes') as A1 on (T.Driver_id = A1.users_id)
where A.users_id is null and A1.users_id is null and T.request_at between '2013-10-01' and '2013-10-03' group by T.request_at
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
/*
595. 大的國家
SQL架構(gòu)
這里有張 World 表
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
如果一個國家的面積超過 300 萬平方公里，或者人口超過 2500 萬，那么這個國家就是大國家。
編寫一個 SQL 查詢，輸出表中所有大國家的名稱、人口和面積。
例如，根據(jù)上表，我們應該輸出:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
*/
/*
方法一：使用 WHERE 子句和 OR【通過】
思路
使用 WHERE 子句過濾所有記錄，獲得滿足條件的國家。
算法
根據(jù)定義，大國家至少滿足以下兩個條件中的一個：
面積超過 300 萬平方公里。
人口超過 2500 萬。
使用下面語句獲得滿足條件 1 的大國家。
MySQL
SELECT name, population, area FROM world WHERE area > 3000000
使用下面語句獲得滿足條件 2 的大國家。
MySQL
SELECT name, population, area FROM world WHERE population > 25000000
使用 OR 將兩個子查詢合并在一起。
MySQL
MySQL
SELECT
name, population, area
FROM
world
WHERE
area > 3000000 OR population > 25000000
;
方法二：使用 WHERE 子句和 UNION【通過】
算法
該方法思路與 方法一 一樣，但是使用 UNION 連接子查詢。
MySQL
MySQL
SELECT
name, population, area
FROM
world
WHERE
area > 3000000
UNION
SELECT
name, population, area
FROM
world
WHERE
population > 25000000
;
注：方法二 比 方法一 運行速度更快，但是它們沒有太大差別。
*/
select name,population,area
from world
where
area > 3000000
union
select name,population,area
from world
where
population > 25000000
select
name ,population,area
from world
where
area > 3000000 or population > 25000000
/*
596. 超過5名學生的課
SQL架構(gòu)
有一個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 |
+---------+
提示：
學生在每個課中不應被重復計算。
*/
/*
方法一：使用 GROUP BY 子句和子查詢【通過】
思路
先統(tǒng)計每門課程的學生數(shù)量，再從中選擇超過 5 名學生的課程。
算法
使用 GROUP BY 和 COUNT 獲得每門課程的學生數(shù)量。
MySQL
SELECT
class, COUNT(DISTINCT student)
FROM
courses
GROUP BY class
;
注：使用 DISTINCT 防止在同一門課中學生被重復計算。
| class | COUNT(student) |
|----------|----------------|
| Biology | 1 |
| Computer | 1 |
| English | 1 |
| Math | 6 |
使用上面查詢結(jié)果的臨時表進行子查詢，篩選學生數(shù)量超過 5 的課程。
MySQL
SELECT
class
FROM
(SELECT
class, COUNT(DISTINCT student) AS num
FROM
courses
GROUP BY class) AS temp_table
WHERE
num >= 5
;
注：COUNT(student) 不能直接在 WHERE 子句中使用，這里將其重命名為 num。
方法二：使用 GROUP BY 和 HAVING 條件【通過】
算法
在 GROUP BY 子句后使用 HAVING 條件是實現(xiàn)子查詢的一種更加簡單直接的方法。
MySQL
MySQL
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
;
*/
select class
from
(select class ,count(distinct student) as number from courses group by class) as a
where a.number >= 5
select class from courses group by class having count(distinct student) >= 5
/*
601. 體育館的人流量
SQL架構(gòu)
表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主鍵
每日人流量信息被記錄在這三列信息中：序號 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行記錄，日期隨著 id 的增加而增加
編寫一個 SQL 查詢以找出每行的人數(shù)大于或等于 100 且 id 連續(xù)的三行或更多行記錄。
返回按 visit_date 升序排列的結(jié)果表。
查詢結(jié)果格式如下所示。
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 為 5、6、7、8 的四行 id 連續(xù)，并且每行都有 >= 100 的人數(shù)記錄。
請注意，即使第 7 行和第 8 行的 visit_date 不是連續(xù)的，輸出也應當包含第 8 行，因為我們只需要考慮 id 連續(xù)的記錄。
不輸出 id 為 2 和 3 的行，因為至少需要三條 id 連續(xù)的記錄。
*/
/*
方法：使用 JOIN 和 WHERE 子句【通過】
思路
在表 stadium 中查詢?nèi)肆髁砍^ 100 的記錄，將查詢結(jié)果與其自身的臨時表連接，再使用 WHERE 子句獲得滿足條件的記錄。
算法
第一步：查詢?nèi)肆髁砍^ 100 的記錄，然后將結(jié)果與其自身的臨時表連接。
MySQL
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
;
| id | date | people | id | date | people | id | date | people |
|----|------------|--------|----|------------|--------|----|------------|--------|
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 |
| 2 | 2017-01-02 | 109 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 |
| 2 | 2017-01-02 | 109 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 3 | 2017-01-03 | 150 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 5 | 2017-01-05 | 145 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 6 | 2017-01-06 | 1455 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
| 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 | 8 | 2017-01-08 | 188 |
共有 6 天人流量超過 100 人，笛卡爾積 后有 216（666） 條記錄。
前 3 列來自表 t1，中間 3 列來自表 t2，最后 3 列來自表 t3。
表 t1，t2 和 t3 相同，需要考慮添加哪些條件能夠得到想要的結(jié)果。以 t1 為例，它有可能是高峰期的第 1 天，第 2 天，或第 3 天。
t1 是高峰期第 1 天：(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
t1 是高峰期第 2 天：(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
t1 是高峰期第 3 天：(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
MySQL
select t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
;
| id | date | people |
|----|------------|--------|
| 7 | 2017-01-07 | 199 |
| 6 | 2017-01-06 | 1455 |
| 8 | 2017-01-08 | 188 |
| 7 | 2017-01-07 | 199 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
可以看到查詢結(jié)果中存在重復的記錄，再使用 DISTINCT 去重。
MySQL
MySQL
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;
*/
select distinct t1.* from
stadium t1,stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and (
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1)
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
)
order by t1.id
/*
620. 有趣的電影
SQL架構(gòu)
某城市開了一家新的電影院，吸引了很多人過來看電影。該電影院特別注意用戶體驗，專門有個 LED顯示板做電影推薦，上面公布著影評和相關(guān)電影描述。
作為該電影院的信息部主管，您需要編寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的并且 id 為奇數(shù) 的影片，結(jié)果請按等級 rating 排列。
例如，下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
對于上面的例子，則正確的輸出是為：
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
*/
select * from cinema where mod(id,2) = 1 and description != 'boring' order by rating desc
/*
626. 換座位
SQL架構(gòu)
小美是一所中學的信息科技老師，她有一張 seat 座位表，平時用來儲存學生名字和與他們相對應的座位 id。
其中縱列的 id 是連續(xù)遞增的
小美想改變相鄰倆學生的座位。
你能不能幫她寫一個 SQL query 來輸出小美想要的結(jié)果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如數(shù)據(jù)輸入的是上表，則輸出結(jié)果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果學生人數(shù)是奇數(shù)，則不需要改變最后一個同學的座位。
*/
/*
方法一：使用 CASE【通過】
算法
對于所有座位 id 是奇數(shù)的學生，修改其 id 為 id+1，如果最后一個座位 id 也是奇數(shù)，則最后一個座位 id 不修改。對于所有座位 id 是偶數(shù)的學生，修改其 id 為 id-1。
首先查詢座位的數(shù)量。
MySQL
SELECT
COUNT(*) AS counts
FROM
seat
然后使用 CASE 條件和 MOD 函數(shù)修改每個學生的座位 id。
MySQL
MySQL
SELECT
(CASE
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
WHEN MOD(id, 2) != 0 AND counts = id THEN id
ELSE id - 1
END) AS id,
student
FROM
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
方法二：使用位操作和 COALESCE()【通過】
算法
使用 (id+1)^1-1 計算交換后每個學生的座位 id。
MySQL
SELECT id, (id+1)^1-1, student FROM seat;
| id | (id+1)^1-1 | student |
|----|------------|---------|
| 1 | 2 | Abbot |
| 2 | 1 | Doris |
| 3 | 4 | Emerson |
| 4 | 3 | Green |
| 5 | 6 | Jeames |
然后連接原來的座位表和更新 id 后的座位表。
MySQL
SELECT
*
FROM
seat s1
LEFT JOIN
seat s2 ON (s1.id+1)^1-1 = s2.id
ORDER BY s1.id;
| id | student | id | student |
|----|---------|----|---------|
| 1 | Abbot | 2 | Doris |
| 2 | Doris | 1 | Abbot |
| 3 | Emerson | 4 | Green |
| 4 | Green | 3 | Emerson |
| 5 | Jeames | | |
注：前兩列來自表 s1，后兩列來自表 s2。
最后輸出 s1.id 和 s2.student。
但是 id=5 的學生，s1.student 正確，s2.student 為 NULL。因此使用 COALESCE() 函數(shù)為最后一行記錄生成正確的輸出。
MySQL
MySQL
SELECT
s1.id, COALESCE(s2.student, s1.student) AS student
FROM
seat s1
LEFT JOIN
seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;
*/
select
( case
when mod(id,2) != 0 and id != seat_counts.counts then id + 1
when mod(id,2) != 0 and id = seat_counts.counts then id
else id - 1
end)
as id,student
from seat,(select count(*) as counts from seat) as seat_counts
order by id asc
/*
COALESCE(value1,value2,...);
COALESCE函數(shù)需要許多參數(shù)，并返回第一個非NULL參數(shù)。如果所有參數(shù)都為NULL，則COALESCE函數(shù)返回NULL。
*/
select s1.id,coalesce(s2.student,s1.student) as student
from seat s1 left join seat s2 on ((s1.id+1)^1) - 1 = s2.id
order by s1.id
/*
627. 變更性別
SQL架構(gòu)
給定一個 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。
交換所有的 f 和 m 值（例如，將所有 f 值更改為 m，反之亦然）。
要求只使用一個更新（Update）語句，并且沒有中間的臨時表。
注意，您必只能寫一個 Update 語句，請不要編寫任何 Select 語句。
例如：
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
運行你所編寫的更新語句之后，將會得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
*/
update salary
set
sex = case sex
when 'm' then 'f'
else 'm'
end;
/*
方法：使用 UPDATE 和 CASE...WHEN
算法
要想動態(tài)地將值設(shè)置成列，我們可以在使用 CASE...WHEN... 流程控制語句的同時使用 UPDATE 語句。
MySQL
UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;
*/
- 行轉(zhuǎn)列
/*
1179. 重新格式化部門表
SQL架構(gòu)
部門表 Department：
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month) 是表的聯(lián)合主鍵。
這個表格有關(guān)于每個部門每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
編寫一個 SQL 查詢來重新格式化表，使得新的表中有一個部門 id 列和一些對應 每個月 的收入（revenue）列。
查詢結(jié)果格式如下面的示例所示：
Department 表：
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
查詢得到的結(jié)果表：
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
注意，結(jié)果表有 13 列 (1個部門 id 列 + 12個月份的收入列)。
*/
select id,
SUM(CASE WHEN month='Jan' THEN revenue END) as Jan_Revenue,
SUM(CASE WHEN month='Feb' THEN revenue END) as Feb_Revenue,
SUM(CASE WHEN month='Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE WHEN month='Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE WHEN month='May' THEN revenue END) AS May_Revenue,
SUM(CASE WHEN month='Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE WHEN month='Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE WHEN month='Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE WHEN month='Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE WHEN month='Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE WHEN month='Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE WHEN month='Dec' THEN revenue END) AS Dec_Revenue
from department
group by id
order by id
- 列轉(zhuǎn)行
建表語句：
CREATE TABLE tb_score1(
id INT(11) NOT NULL auto_increment,
userid VARCHAR(20) NOT NULL COMMENT '用戶id',
cn_score DOUBLE COMMENT '語文成績',
math_score DOUBLE COMMENT '數(shù)學成績',
en_score DOUBLE COMMENT '英語成績',
po_score DOUBLE COMMENT '政治成績',
PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
插入數(shù)據(jù)：
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);
查詢數(shù)據(jù)表中的內(nèi)容（即轉(zhuǎn)換前的結(jié)果）
SELECT * FROM tb_score1
image.png
轉(zhuǎn)換后：
image.png
本質(zhì)是將userid的每個科目分數(shù)分散成一條記錄顯示出來。
直接上SQL：
SELECT userid,'語文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'數(shù)學' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英語' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid
這里將每個userid對應的多個科目的成績查出來，通過UNION ALL將結(jié)果集加起來，達到上圖的效果。
附：UNION與UNION ALL的區(qū)別（摘）：
1.對重復結(jié)果的處理：UNION會去掉重復記錄，UNION ALL不會；
2.對排序的處理：UNION會排序，UNION ALL只是簡單地將兩個結(jié)果集合并；
3.效率方面的區(qū)別：因為UNION 會做去重和排序處理，因此效率比UNION ALL慢很多；
