新開一個帖子記錄SQL的漫漫長征~
Day 1
leecode SQL 175
表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
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/combine-two-tables
# Write your MySQL query statement below
SELECT FirstName, LastName, City, State
FROM Person as p LEFT JOIN Address as a ON p.PersonId = a.PersonId;
Left Join 可以存在null袍辞。
leecode SQL 176
編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 造寝。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表磕洪，SQL查詢應(yīng)該返回 200 作為第二高的薪水。如果不存在第二高的薪水诫龙，那么查詢應(yīng)返回 null析显。
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/second-highest-salary
# Write your MySQL query statement below
SELECT MAX(Salary) as SecondHighestSalary
FROM Employee
WHERE Salary < ( SELECT MAX(Salary)
FROM Employee
)
奇奇怪怪的思路，第二高就是把第一木大了签赃。
Day 2
SQL 181
Employee 表包含所有員工谷异，他們的經(jīng)理也屬于員工。每個員工都有一個 Id锦聊，此外還有一列對應(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 表，編寫一個 SQL 查詢孔庭，該查詢可以獲取收入超過他們經(jīng)理的員工的姓名尺上。在上面的表格中，Joe 是唯一一個收入超過他的經(jīng)理的員工圆到。
+----------+
| Employee |
+----------+
| Joe |
+----------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/employees-earning-more-than-their-managers
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有怎抛。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處构资。
(待解決！Ｔ纱亍吐绵！)
SELECT
E.Name AS Employee
FROM
Employee AS E
INNER JOIN Employee AS M
ON E.ManagerId = M.Id
AND E.Salary > M.Salary;
這里自聯(lián)結(jié)的表現(xiàn)比其他方式速度快很多。
SQL 182
編寫一個 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 |
+---------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/duplicate-emails
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有耙饰。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)纹笼，非商業(yè)轉(zhuǎn)載請注明出處。
# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1;
Day 3
SQL 183
某網(wǎng)站包含兩個表苟跪，Customers 表和 Orders 表廷痘。編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶件已。
Customers 表：
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表：
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如給定上述表格笋额，你的查詢應(yīng)返回：
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/customers-who-never-order
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)篷扩，非商業(yè)轉(zhuǎn)載請注明出處兄猩。
# Write your MySQL query statement below
select Name Customers
from Customers c left join Orders o on c.Id = o.CustomerId
where CustomerId is null;
SELECT NAME AS CUSTOMERS
FROM CUSTOMERS
WHERE ID NOT IN (SELECT CUSTOMERID
FROM ORDERS);
第二種方法速度更快！
SQL 196
編寫一個 SQL 查詢，來刪除 Person 表中所有重復(fù)的電子郵箱枢冤，重復(fù)的郵箱里只保留 Id 最小 的那個鸠姨。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是這個表的主鍵。
例如淹真，在運行你的查詢語句之后讶迁，上面的 Person 表應(yīng)返回以下幾行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/delete-duplicate-emails
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)趟咆，非商業(yè)轉(zhuǎn)載請注明出處添瓷。
# Write your MySQL query statement below
DELETE p1
FROM Person p1, Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id;
可以先select找出，id較大的重復(fù)的郵件值纱，隨后刪除鳞贷。
Day 4
SQL 197
表 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 的溫度比前一天高（20 -> 30）
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/rising-temperature
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)疆偿，非商業(yè)轉(zhuǎn)載請注明出處咱筛。
SELECT w2.Id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature < w2.Temperature
DATEDIFF() 函數(shù)返回兩個日期之間的天數(shù)。
(這里可用Join杆故，也可以不用)
天數(shù)相差為一迅箩，同時溫度大。
SQL 595
這里有張 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ù)上表龄砰，我們應(yīng)該輸出:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/big-countries
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)讨衣，非商業(yè)轉(zhuǎn)載請注明出處换棚。
# Write your MySQL query statement below
select name, population, area
from World
where area > 3000000 or population > 25000000;
SQL
有一個courses 表 ，有: student (學(xué)生) 和 class (課程)反镇。
請列出所有超過或等于5名學(xué)生的課圃泡。
例如，表：
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
應(yīng)該輸出:
+---------+
| class |
+---------+
| Math |
+---------+
提示：
學(xué)生在每個課中不應(yīng)被重復(fù)計算愿险。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/classes-more-than-5-students
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有颇蜡。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)价说，非商業(yè)轉(zhuǎn)載請注明出處。
SELECT
class
FROM
courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5
;
HAVING()函數(shù)：
在 SQL 中增加 HAVING 子句原因是风秤，WHERE 關(guān)鍵字無法與聚合函數(shù)一起使用鳖目。
HAVING 子句可以讓我們篩選分組后的各組數(shù)據(jù)。
Day 5
SQL 620
今天復(fù)習(xí)一下運算符的知識：
SQL 算術(shù)運算符
假設(shè)變量 a 的值是：10缤弦，變量 b 的值是：20领迈，以下為各運算符執(zhí)行結(jié)果：
| + 加法，執(zhí)行加法運算碍沐。 | a + b 得到 30
| - 減法狸捅，執(zhí)行減法運算。| a - b 得到 -10
| * 乘法累提，執(zhí)行乘法運算尘喝。 | a * b 得到 200
| / 用左操作數(shù)除以右操作數(shù)。 | b / a 得到 2
| % 用左操作數(shù)除以右操作數(shù)并返回余數(shù)斋陪。| b % a 得到 0
SQL 比較運算符
假設(shè)變量 a 的值是：10朽褪，變量 b 的值是：20，以下為各運算符執(zhí)行結(jié)果：
| 運算符 | 描述 | 例子 |
| = 檢查兩個操作數(shù)的值是否相等无虚，如果是缔赠，則條件為真(true)。
| (a = b) is false. |
| != 檢查兩個操作數(shù)的值是否相等友题，如果值不相等則條件為真(true)嗤堰。
| (a != b) is true. |
| <> 檢查兩個操作數(shù)的值是否相等，如果值不相等則條件為真(true)度宦。 | (a <> b) is true. |
| > 檢查左操作數(shù)的值是否大于右操作數(shù)的值踢匣，如果是，則條件為真(true)斗埂。
| (a > b) is false. |
| < 檢查左操作數(shù)的值是否小于右操作數(shù)的值符糊，如果是凫海，則條件為真(true)呛凶。 | (a < b) is true. |
| >= 檢查左操作數(shù)的值是否大于或等于右操作數(shù)的值，如果是行贪，則條件為真(true)漾稀。 | (a >= b) is false |
| <= 檢查左操作數(shù)的值是否小于或等于右操作數(shù)的值，如果是建瘫，則條件為真(true)崭捍。 | (a <= b) is true. |
| !< 檢查左操作數(shù)的值是否不小于右操作數(shù)的值，如果是啰脚，則條件變?yōu)檎?true)殷蛇。 | (a !< b) is false. |
| !> 檢查左操作數(shù)的值是否不大于右操作數(shù)的值实夹，如果是，則條件變?yōu)檎?true)粒梦。 | (a !> b) is true. |
SQL 邏輯運算符：
這是在 SQL 所有的邏輯運算符的列表亮航。
| 運算符 | 描述 |
| ALL | ALL運算符用于將值與另一個值集中的所有值進行比較。 |
| AND | AND運算符允許在SQL語句的WHERE子句中指定多個條件匀们。
|
| ANY | ANY運算符用于根據(jù)條件將值與列表中的任何適用值進行比較缴淋。 |
| BETWEEN | BETWEEN運算符用于搜索在給定最小值和最大值內(nèi)的值。
|
| EXISTS | EXISTS運算符用于搜索指定表中是否存在滿足特定條件的行泄朴。 |
| IN | IN運算符用于將值與已指定的文字值列表進行比較重抖。 |
| LIKE | LIKE運算符用于使用通配符運算符將值與類似值進行比較。 |
| NOT | NOT運算符反轉(zhuǎn)使用它的邏輯運算符的含義祖灰。 例如：NOT EXISTS, NOT BETWEEN, NOT IN等等钟沛，這是一個否定運算符。
|
| OR | OR運算符用于組合SQL語句的WHERE子句中的多個條件夫植。 |
| IS NULL | IS NULL運算符用于將值與NULL值進行比較讹剔。 |
| UNIQUE | UNIQUE運算符搜索指定表的每一行的唯一性(無重復(fù)項)。 |
某城市開了一家新的電影院详民，吸引了很多人過來看電影延欠。該電影院特別注意用戶體驗，專門有個 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 |
+---------+-----------+--------------+-----------+
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/not-boring-movies
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)纠吴，非商業(yè)轉(zhuǎn)載請注明出處硬鞍。
# Write your MySQL query statement below
select id, movie, description, rating
from cinema
where description <> "boring" and id%2 <> 0
order by rating desc;
SQL 627
給定一個 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 |
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/swap-salary
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有独泞。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處苔埋。
# Write your MySQL query statement below
update salary
set sex = case sex
when "m" then "f"
else "m"
end;
補充一下 “case when” 的用法：
推薦閱讀這篇懦砂，這里就不ctrl + c 了
http://www.reibang.com/p/113b21734353
Day 6
出發(fā)前的最后一晚了，剛好也是免費的題要刷完了组橄。
SQL 1179
部門表 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 列和一些對應(yīng) 每個月 的收入（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個月份的收入列)屠升。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/reformat-department-table
著作權(quán)歸領(lǐng)扣網(wǎng)絡(luò)所有。商業(yè)轉(zhuǎn)載請聯(lián)系官方授權(quán)狭郑，非商業(yè)轉(zhuǎn)載請注明出處腹暖。
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;
上述代碼的執(zhí)行語序問題：
1). from子句，然后 group by 子句 翰萨，大致結(jié)果是這樣(引用圖)
2). select子句 , 共13個字段（列）脏答，第一列為 id , 即部門編號，這個較好理解亩鬼。
3). 然后理解剩余12列的值殖告，每有一個id必然會有其對應(yīng)的12列(月份)的值。當id=1時雳锋，進入到經(jīng)過分組的id為1 的表中黄绩，首先為jan_revenue列賦值，遍歷該id表的所有行玷过，通過case when 選定對應(yīng)的revenue或null,然后將選定的這些值sum(詳見注解) 爽丹，賦值給id的jan_revenue。如此冶匹，依次賦值給其他11列习劫。同理咆瘟，select 其他id值時進入對應(yīng)id表內(nèi)...
注解：
{ 遍歷整個id表嚼隘，針對每個月或者說針對每條case when 語句，其實只有一個renvenue或根本沒有袒餐，即都是null值飞蛹。
①'只有一個revenue' : 按照表的定義谤狡，每個id分組表內(nèi)month列的值絕不會有重復(fù)，也就是說一個部門在某個月的的收入不會分為兩條來記錄卧檐，該說法也與題目中 'id與month 為聯(lián)合主鍵'的說法一致(主鍵唯一)
②'都是null值'：id表中沒有一行的month值為當前case when月份所對應(yīng)的墓懂，即在數(shù)據(jù)庫中，該部門沒有錄入當月的收入霉囚。
③'使用sum聚合函數(shù)': 因為‘只有一個revenue’,'都是null值'捕仔，所以使用sum,max,min 等聚合函數(shù)效果都是一樣的。使用sum更易讓人忽略細節(jié)盈罐，且更易于讓人粗略的榜跌，模糊的結(jié)合group by 接受并理解。
④'不使用類似的聚合函數(shù)'：不可以盅粪。group by 分組后钓葫，在mysql中，若不使用聚合函數(shù)來提取值票顾，直接select只會select出當前id表的第一行(某個月份)础浮，這也意味著在為當前id的12列賦值時，每次都是遍歷這一行奠骄。結(jié)果就是只有與與改行月份對應(yīng)的那個case when 語句的列會被賦值為revenue,其他皆判斷為不符合（即該id的其他列都是null值）
⑤其實不太能理清sum與case語句執(zhí)行的先后關(guān)系豆同，個人認定為先case ,后sum ：推翻上述的過程' 3). ', 為某id部門的某個月(對應(yīng)的列)賦值時，先進入對應(yīng)id表含鳞，先case when 诱告，遍歷group by 后該id表的第一行(結(jié)合④理解)，然后該id的該列被賦值為null或者revenue值民晒，然后發(fā)現(xiàn)外面嵌套聚合函數(shù)sum，返回case when 完整的遍歷該id表靴姿，最終根據(jù)具體的聚合函數(shù)形式(此處為sum)得到該id(部門)該列(月份)的最終正確值佛吓。該id其他列维雇，以及其他id的各列皆是如此吱型。}
作者：jiachang-a
鏈接：https://leetcode-cn.com/problems/reformat-department-table/solution/guan-yu-pu-bian-jiu-jie-de-group-by-case-d82v/
來源：力扣（LeetCode）
著作權(quán)歸作者所有津滞。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)咪鲜，非商業(yè)轉(zhuǎn)載請注明出處疟丙。
