MySQL 排序、分页查询、聚合查询
文章目錄
- 1. 排序
- 2. 分頁查詢
- 3. 聚合查詢
- 3.1 分組聚合 GROUP BY
- 練習 LeetCode 176. 第二高的薪水
- 練習 LeetCode 177. 第N高的薪水
- 練習 LeetCode 182. 查找重復的電子郵箱
- 練習 LeetCode 620. 有趣的電影
- 練習 LeetCode 183. 從不訂購的客戶
- 練習 LeetCode 596. 超過5名學生的課
- 練習 LeetCode 586. 訂單最多的客戶
- 練習 LeetCode 1082. 銷售分析 I
- 練習 LeetCode 1050. 合作過至少三次的演員和導演
- 練習 LeetCode 1148. 文章瀏覽 I
- 練習 LeetCode 511. 游戲玩法分析 I
- 練習 LeetCode 1485. 按日期分組銷售產品
- 練習 LeetCode 1407. 排名靠前的旅行者
學習自 廖雪峰的官方網站
1. 排序
SELECT查詢時，是根據主鍵排序
- 根據其他條件排序，可以加上ORDER BY子句（默認升序 ASC，可省略）
- 降序DESC
- 多條件排序
- 如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面
2. 分頁查詢
查詢時，如果結果集數據量很大，分頁顯示
可以通過LIMIT <M> OFFSET <N>子句實現。每次顯示最多 M 條，從第 N 條記錄開始算
SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0; # 每頁3條記錄，從第0條開始OFFSET超過了查詢的最大數量不會報錯，得到一個空集
OFFSET是可選的，如果只寫LIMIT 15 == LIMIT 15 OFFSET 0
在MySQL中，LIMIT 15 OFFSET 30 == LIMIT 30, 15
使用LIMIT <M> OFFSET <N>分頁時，隨著N越來越大，查詢效率也會越來越低
3. 聚合查詢
SQL內置的COUNT()函數查詢行數
SELECT COUNT(*) FROM students; # 返回一個二維表 ，一行一列共有10條記錄
設置別名
| SUM | 計算某一列的合計值，該列必須為數值類型 |
| AVG | 計算某一列的平均值，該列必須為數值類型 |
| MAX | 計算某一列的最大值，可以對字符串排序 |
| MIN | 計算某一列的最小值，可以對字符串排序 |
特別注意：WHERE沒有匹配到任何行，COUNT()會返回0，而SUM()、AVG()、MAX()和MIN()會返回 NULL
3.1 分組聚合 GROUP BY
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
使用多個列進行分組。例如，統計各班的男女人數：
練習 LeetCode 176. 第二高的薪水
題目：
編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+例如上述 Employee 表，SQL查詢應該返回 200 作為第二高的薪水。
如果不存在第二高的薪水，那么查詢應返回 null。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/second-highest-salary
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
- DISTINCT去重，題目要求不能并列（200,200的話，第二高為NULL）
194 ms
練習 LeetCode 177. 第N高的薪水
題目：
編寫一個 SQL 查詢，獲取 Employee 表中第 n 高的薪水（Salary）。
例如上述 Employee 表，n = 2 時，應返回第二高的薪水 200。
如果不存在第 n 高的薪水，那么查詢應返回 null。
來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/nth-highest-salary
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
- 跟上題一樣，注意提前設置 N-1的值，不支持 OFFSET N-1寫法
246 ms
練習 LeetCode 182. 查找重復的電子郵箱
題目：
編寫一個 SQL 查詢，查找 Person 表中所有重復的電子郵箱。
Create table If Not Exists Person (Id int, Email varchar(255)) Truncate table Person insert into Person (Id, Email) values ('1', 'a@b.com') insert into Person (Id, Email) values ('2', 'c@d.com') insert into Person (Id, Email) values ('3', 'a@b.com') 示例： +----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ 根據以上輸入，你的查詢應返回以下結果：+---------+ | Email | +---------+ | a@b.com | +---------+ 說明：所有電子郵箱都是小寫字母。來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/duplicate-emails
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
使用 HAVING 關鍵字
# Write your MySQL query statement below SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1;265 ms
練習 LeetCode 620. 有趣的電影
題目：
某城市開了一家新的電影院，吸引了很多人過來看電影。
該電影院特別注意用戶體驗，專門有個 LED顯示板做電影推薦，上面公布著影評和相關電影描述。
作為該電影院的信息部主管，您需要編寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的并且 id 為奇數 的影片，結果請按等級 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
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below SELECT * FROM cinema WHERE description != 'boring' AND id%2 = 1 ORDER BY rating DESC或者用 mod(id,2) = 1
!=也可以用<>
198 ms
練習 LeetCode 183. 從不訂購的客戶
題目：
某網站包含兩個表，Customers 表和 Orders 表。
編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶。
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/customers-who-never-order
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
NOT IN 關鍵字
# Write your MySQL query statement below SELECT C.Name Customers FROM Customers C WHERE C.Id NOT IN (SELECT CustomerId FROM Orders )或者
# Write your MySQL query statement below SELECT C.Name Customers FROM Customers C LEFT OUTER JOIN Orders O ON C.Id = O.CustomerId WHERE O.CustomerId is null363 ms
練習 LeetCode 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 | +---------+ Note: 學生在每個課中不應被重復計算。（有課程中，重復出現2次A，只算一次）來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/classes-more-than-5-students
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5;206 ms
練習 LeetCode 586. 訂單最多的客戶
在表 orders 中找到訂單數最多客戶對應的 customer_number 。
數據保證訂單數最多的顧客恰好只有一位。
表 orders 定義如下：
| Column | Type | |-------------------|-----------| | order_number (PK) | int | | customer_number | int | | order_date | date | | required_date | date | | shipped_date | date | | status | char(15) | | comment | char(200) |樣例輸入
| order_number | customer_number | order_date | required_date | shipped_date | status | comment | |--------------|-----------------|------------|---------------|--------------|--------|---------| | 1 | 1 | 2017-04-09 | 2017-04-13 | 2017-04-12 | Closed | | | 2 | 2 | 2017-04-15 | 2017-04-20 | 2017-04-18 | Closed | | | 3 | 3 | 2017-04-16 | 2017-04-25 | 2017-04-20 | Closed | | | 4 | 3 | 2017-04-18 | 2017-04-28 | 2017-04-25 | Closed | | 樣例輸出| customer_number | |-----------------| | 3 |解釋
customer_number 為 ‘3’ 的顧客有兩個訂單，比顧客 ‘1’ 或者 ‘2’ 都要多，因為他們只有一個訂單
所以結果是該顧客的 customer_number ，也就是 3 。
進階： 如果有多位顧客訂單數并列最多，你能找到他們所有的 customer_number 嗎？
來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/customer-placing-the-largest-number-of-orders
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below select t.customer_number customer_number from (select customer_number, count(customer_number) amount from ordersgroup by customer_numberorder by amount desclimit 1 offset 0 ) tor
# Write your MySQL query statement below select customer_number from orders group by customer_number order by count(customer_number) desc limit 1練習 LeetCode 1082. 銷售分析 I
產品表：Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | | unit_price | int | +--------------+---------+product_id 是這個表的主鍵.
銷售表：Sales
+-------------+---------+ | Column Name | Type | +-------------+---------+ | seller_id | int | | product_id | int | | buyer_id | int | | sale_date | date | | quantity | int | | price | int | +------ ------+---------+這個表沒有主鍵，它可以有重復的行.
product_id 是 Product 表的外鍵.
編寫一個 SQL 查詢，查詢總銷售額最高的銷售者，如果有并列的，就都展示出來。
查詢結果格式如下所示：
Product 表： +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+Sales 表： +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+Result 表： +-------------+ | seller_id | +-------------+ | 1 | | 3 | +-------------+ Id 為 1 和 3 的銷售者，銷售總金額都為最高的 2800。來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/sales-analysis-i
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below select seller_id from Sales group by seller_id having sum(price) = (select sum(price) as totalincome from Salesgroup by seller_idorder by totalincome desclimit 1)or
- all 函數，所有的都要滿足
練習 LeetCode 1050. 合作過至少三次的演員和導演
ActorDirector 表：
+-------------+---------+ | Column Name | Type | +-------------+---------+ | actor_id | int | | director_id | int | | timestamp | int | +-------------+---------+timestamp 是這張表的主鍵.
寫一條SQL查詢語句獲取合作過至少三次的演員和導演的 id 對 (actor_id, director_id)
示例：
ActorDirector 表： +-------------+-------------+-------------+ | actor_id | director_id | timestamp | +-------------+-------------+-------------+ | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 1 | 6 | +-------------+-------------+-------------+Result 表： +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ 唯一的 id 對是 (1, 1)，他們恰好合作了 3 次。來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/actors-and-directors-who-cooperated-at-least-three-times
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below select actor_id, director_id from ActorDirector group by actor_id, director_id having count(*) >= 3練習 LeetCode 1148. 文章瀏覽 I
Views 表：
+---------------+---------+ | Column Name | Type | +---------------+---------+ | article_id | int | | author_id | int | | viewer_id | int | | view_date | date | +---------------+---------+此表無主鍵，因此可能會存在重復行。
此表的每一行都表示某人在某天瀏覽了某位作者的某篇文章。
請注意，同一人的 author_id 和 viewer_id 是相同的。
請編寫一條 SQL 查詢以找出所有瀏覽過自己文章的作者，結果按照 id 升序排列。
查詢結果的格式如下所示：
Views 表： +------------+-----------+-----------+------------+ | article_id | author_id | viewer_id | view_date | +------------+-----------+-----------+------------+ | 1 | 3 | 5 | 2019-08-01 | | 1 | 3 | 6 | 2019-08-02 | | 2 | 7 | 7 | 2019-08-01 | | 2 | 7 | 6 | 2019-08-02 | | 4 | 7 | 1 | 2019-07-22 | | 3 | 4 | 4 | 2019-07-21 | | 3 | 4 | 4 | 2019-07-21 | +------------+-----------+-----------+------------+結果表： +------+ | id | +------+ | 4 | | 7 | +------+來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/article-views-i
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
- distinct 去重
練習 LeetCode 511. 游戲玩法分析 I
活動表 Activity：
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+表的主鍵是 (player_id, event_date)。
這張表展示了一些游戲玩家在游戲平臺上的行為活動。
每行數據記錄了一名玩家在退出平臺之前，當天使用同一臺設備登錄平臺后打開的游戲的數目（可能是 0 個）。
寫一條 SQL 查詢語句獲取每位玩家 第一次登陸平臺的日期。
查詢結果的格式如下所示：
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 | +-----------+-------------+來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/game-play-analysis-i
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below select player_id, min(event_date) first_login from Activity group by player_id練習 LeetCode 1485. 按日期分組銷售產品
表 Activities：
+-------------+---------+ | 列名 | 類型 | +-------------+---------+ | sell_date | date | | product | varchar | +-------------+---------+此表沒有主鍵，它可能包含重復項。
此表的每一行都包含產品名稱和在市場上銷售的日期。
編寫一個 SQL 查詢來查找每個日期、銷售的不同產品的數量及其名稱。
每個日期的銷售產品名稱應按詞典序排列。
返回按 sell_date 排序的結果表。
查詢結果格式如下例所示。
Activities 表： +------------+-------------+ | sell_date | product | +------------+-------------+ | 2020-05-30 | Headphone | | 2020-06-01 | Pencil | | 2020-06-02 | Mask | | 2020-05-30 | Basketball | | 2020-06-01 | Bible | | 2020-06-02 | Mask | | 2020-05-30 | T-Shirt | +------------+-------------+Result 表： +------------+----------+------------------------------+ | sell_date | num_sold | products | +------------+----------+------------------------------+ | 2020-05-30 | 3 | Basketball,Headphone,T-shirt | | 2020-06-01 | 2 | Bible,Pencil | | 2020-06-02 | 1 | Mask | +------------+----------+------------------------------+ 對于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按詞典序排列，并用逗號 ',' 分隔。 對于2020-06-01，出售的物品是 (Pencil, Bible)，按詞典序排列，并用逗號分隔。 對于2020-06-02，出售的物品是 (Mask)，只需返回該物品名。來源：力扣（LeetCode）
鏈接：https://leetcode-cn.com/problems/group-sold-products-by-the-date
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
- group_concat()
group by 產生的同一個分組中的值連接起來，返回一個字符串結果。 - 語法：group_concat( [distinct] 要連接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
- distinct可以去重，order by子句 排序；separator是一個字符串值，缺省為一個逗號
練習 LeetCode 1407. 排名靠前的旅行者
表單: Users
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id 是該表單主鍵. name 是用戶名字.表單: Rides
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | user_id | int | | distance | int | +---------------+---------+ id 是該表單主鍵. user_id 是本次行程的用戶的 id, 而該用戶此次行程距離為 distance.寫一段 SQL , 報告每個用戶的旅行距離.
返回的結果表單, 以 travelled_distance 降序排列,
如果有兩個或者更多的用戶旅行了相同的距離, 那么再以 name 升序排列.
查詢結果格式, 如下例所示.
Users 表單: +------+-----------+ | id | name | +------+-----------+ | 1 | Alice | | 2 | Bob | | 3 | Alex | | 4 | Donald | | 7 | Lee | | 13 | Jonathan | | 19 | Elvis | +------+-----------+Rides 表單: +------+----------+----------+ | id | user_id | distance | +------+----------+----------+ | 1 | 1 | 120 | | 2 | 2 | 317 | | 3 | 3 | 222 | | 4 | 7 | 100 | | 5 | 13 | 312 | | 6 | 19 | 50 | | 7 | 7 | 120 | | 8 | 19 | 400 | | 9 | 7 | 230 | +------+----------+----------+Result 表單: +----------+--------------------+ | name | travelled_distance | +----------+--------------------+ | Elvis | 450 | | Lee | 450 | | Bob | 317 | | Jonathan | 312 | | Alex | 222 | | Alice | 120 | | Donald | 0 | +----------+--------------------+ Elvis 和 Lee 旅行了 450 英里, Elvis 是排名靠前的旅行者, 因為他的名字在字母表上的排序比 Lee 更小. Bob, Jonathan, Alex 和 Alice 只有一次行程, 我們只按此次行程的全部距離對他們排序. Donald 沒有任何行程, 他的旅行距離為 0.來源：力扣（LeetCode） 鏈接：https://leetcode-cn.com/problems/top-travellers
著作權歸領扣網絡所有。商業轉載請聯系官方授權，非商業轉載請注明出處。
解題：
# Write your MySQL query statement below select name, ifnull(dis,0) travelled_distance from (select user_id id, sum(distance) disfrom Ridesgroup by user_id ) t right join Users using(id) order by travelled_distance desc, nameor
# Write your MySQL query statement below select name, ifnull(sum(distance),0) travelled_distance from Users left join Rides on Users.id = Rides.user_id group by Users.id order by travelled_distance desc, name總結
以上是生活随笔為你收集整理的MySQL 排序、分页查询、聚合查询的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： [scikit-learn 机器学习]
- 下一篇： LeetCode 333. 最大 BST