MySQL 排序、分页查询、聚合查询

学习自 廖雪峰的官方网站

1. 排序

SELECT查询时,是根据主键排序

  • 根据其他条件排序,可以加上ORDER BY子句(默认升序 ASC,可省略)
SELECT id, name, gender, score FROM students ORDER BY score;

在这里插入图片描述

  • 降序DESC
ORDER BY <列名> DESC  # 降序排列
SELECT id, name, gender, score FROM students ORDER BY score DESC;
  • 多条件排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
# 先根据分数降序,然后根据性别
  • 如果有WHERE子句,那么ORDER BY子句要放到WHERE子句后面
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

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条记录
在这里插入图片描述
设置别名

SELECT COUNT(*) num FROM students;

在这里插入图片描述

函数说明
SUM计算某一列的合计值,该列必须为数值类型
AVG计算某一列的平均值,该列必须为数值类型
MAX计算某一列的最大值,可以对字符串排序
MIN计算某一列的最小值,可以对字符串排序
SELECT AVG(score) average FROM students WHERE gender = 'M';

在这里插入图片描述

特别注意:WHERE没有匹配到任何行,COUNT()会返回0,而SUM()、AVG()、MAX()和MIN()会返回 NULL

3.1 分组聚合 GROUP BY

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;

在这里插入图片描述
使用多个列进行分组。例如,统计各班的男女人数:

SELECT class_id, gender, COUNT(*) num FROM students 
GROUP BY class_id, gender;

在这里插入图片描述

每个班级的平均分
SELECT class_id, AVG(score) avgsc FROM students
GROUP BY class_id ORDER BY avgsc DESC;

在这里插入图片描述

每个班级男生和女生的平均分
SELECT class_id, gender, AVG(score) avgsc FROM students
GROUP BY class_id, gender ORDER BY avgsc DESC;

在这里插入图片描述

练习 LeetCode 176. 第二高的薪水

题目:

编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。
如果不存在第二高的薪水,那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/second-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

解题:

  • DISTINCT去重,题目要求不能并列(200,200的话,第二高为NULL)
# Write your MySQL query statement below
SELECT 
(
    SELECT DISTINCT Salary
    FROM Employee ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
)
SecondHighestSalary

194 ms

练习 LeetCode 177. 第N高的薪水

题目:
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。
如果不存在第 n 高的薪水,那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/nth-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。

解题:

  • 跟上题一样,注意提前设置 N-1的值,不支持 OFFSET N-1写法
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary
      FROM Employee ORDER BY Salary DESC
      LIMIT 1 OFFSET n
  );
END

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 查询,找出所有从不订购任何东西的客户。

Customers 表:
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

来源:力扣(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 null

363 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 orders
    group by customer_number
    order by amount desc
    limit 1 offset 0
) t

or

# 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 为 13 的销售者,销售总金额都为最高的 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 Sales
            group by seller_id
            order by totalincome desc
            limit 1
        )

or

  • all 函数,所有的都要满足
# Write your MySQL query statement below
select seller_id from Sales
group by seller_id
having sum(price) >=
        all(
            select sum(price) from Sales
            group by seller_id
        )

练习 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 去重
# Write your MySQL query statement below
select distinct author_id as id from Views
where viewer_id = author_id
order by id

练习 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是一个字符串值,缺省为一个逗号
# Write your MySQL query statement below
select sell_date, count(distinct product) num_sold, 
    group_concat(distinct product order by product separator ',') products from Activities
group by sell_date
order by sell_date

练习 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) dis
    from Rides
    group by user_id
) t right join Users
using(id)
order by travelled_distance desc, name

or

# 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
  • 27
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 32
    评论
评论 32
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Michael阿明

如果可以,请点赞留言支持我哦!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值