跳到主要内容

排序与分组

排序（ORDER BY）和分组（GROUP BY）是 SQL 中最常用的数据处理操作。排序让结果有序可读，分组则是数据统计分析的基础。掌握这两个操作，是写出高质量 SQL 的必备技能。

核心概念​

ORDER BY 排序​

ORDER BY 用于对查询结果进行排序，可以按一个或多个字段排序，支持升序（ASC）和降序（DESC）。

基本语法：

SELECT 列名
FROM 表名
ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;

关键点：

  • 默认是升序（ASC），可以省略
  • 多字段排序时，按字段顺序依次排序
  • NULL 值在升序时排在最前，降序时排在最后

GROUP BY 分组​

GROUP BY 将数据按指定列的值分成多个"组"，然后对每组进行聚合计算。

分组的工作原理：

WHERE vs HAVING​

这是初学者最容易混淆的地方：

子句作用时机过滤对象能否用聚合函数
WHERE分组前原始行不能
HAVING分组后分组结果能

理解执行顺序：

SELECT department, COUNT(*) as cnt  -- 5. 选择列
FROM employees                        -- 1. 从表获取数据
WHERE status = 'active'              -- 2. 过滤行（分组前）
GROUP BY department                  -- 3. 分组
HAVING COUNT(*) >= 5                 -- 4. 过滤组（分组后）
ORDER BY cnt DESC                    -- 6. 排序
LIMIT 10;                            -- 7. 限制行数

2356. 每位教师所教授的科目种类的数量 (Number of Unique Subjects Taught by Each Teacher)​

题目描述​

Teacher 表保存了教师教授科目和在哪个系教授的信息：

  • teacher_id: 教师 ID。
  • subject_id: 科目 ID。
  • dept_id: 系 ID。

请编写一个解决方案，计算每位老师在大学里教授的 不同 科目的数量。

解题思路​

这道题考查 分组统计 + 去重计数。

分析过程：

  1. 分组依据：按教师 ID 分组
  2. 统计内容：每个教师教授的不同科目数量
  3. 去重：同一教师可能在不同系教授同一科目，需要 DISTINCT

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)：

函数说明
COUNT(*)统计所有行数（包括 NULL）
COUNT(column)统计该列非 NULL 值的数量
COUNT(DISTINCT column)统计该列不同值的数量

SQL 实现 (MySQL)​

SELECT teacher_id, COUNT(DISTINCT subject_id) AS cnt 
FROM Teacher 
GROUP BY teacher_id;

代码解析：

  • GROUP BY teacher_id：按教师分组
  • COUNT(DISTINCT subject_id)：统计每个教师的不同科目数

1141. 查询近30天活跃用户数 (User Activity for the Past 30 Days I)​

题目描述​

Activity 表记录了社交媒体网站的用户活动：

  • user_id: 用户 ID。
  • session_id: 会话 ID。
  • activity_date: 活动日期。
  • activity_type: 活动类型 ('open_session', 'end_session', 'scroll_down', 'send_message')。

请编写一个解决方案，找出截至 2019-07-27（包含当天）近 30 天内，每日活跃用户的数量（至少进行过一次活动的用户即为活跃用户）。

解题思路​

这道题考查 日期范围过滤 + 分组统计 + 去重计数。

日期计算分析：

"近 30 天"的起始日期计算：

  • 结束日期：2019-07-27（包含）
  • 开始日期：2019-07-27 - 29 = 2019-06-28
  • 日期范围：[2019-06-28, 2019-07-27]，共 30 天

为什么是减 29 不是减 30？

因为"近 30 天包含当天"意味着当天 + 前 29 天 = 30 天。如果是减 30，就变成了 31 天。

活跃用户定义：

  • 一个用户在同一天可能有多次活动
  • 活跃用户数需要去重：COUNT(DISTINCT user_id)

SQL 实现 (MySQL)​

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users 
FROM Activity 
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27' 
GROUP BY activity_date;

代码解析：

  • BETWEEN '2019-06-28' AND '2019-07-27'：日期范围过滤
  • GROUP BY activity_date：按日期分组
  • COUNT(DISTINCT user_id)：统计每天的不同用户数

1084. 销售分析 III (Sales Analysis III)​

题目描述​

你有两张表：

  • Product: 包含 product_id (主键) 和 product_name。
  • Sales: 记录销售情况，包含 product_id, buyer_id, sale_date, quantity, price。

请编写一个解决方案，报告 仅 在 2019 年第一季度（即 2019-01-01 至 2019-03-31）销售的产品。

解题思路​

这道题考查 HAVING 子句的复杂条件。

关键理解：

"仅"在第一季度销售意味着：

  • 所有销售记录的日期都在第一季度内
  • 等价于：最小日期和最大日期都在第一季度范围内

为什么不能只用 WHERE？

如果只用 WHERE sale_date BETWEEN '2019-01-01' AND '2019-03-31'，会过滤掉其他季度的销售记录，但不会排除那些"既在第一季度卖过，也在其他季度卖过"的产品。

正确思路：

SQL 实现 (MySQL)​

SELECT product_id, product_name 
FROM Sales s
JOIN Product p USING(product_id)
GROUP BY product_id 
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31';

代码解析：

  • GROUP BY product_id：按产品分组
  • MIN(sale_date)：该产品最早的销售日期
  • MAX(sale_date)：该产品最晚的销售日期
  • HAVING 条件确保日期范围完全在第一季度内

596. 超过 5 名学生的课 (Classes More Than 5 Students)​

题目描述​

Courses 表记录了学生选课的情况：

  • student: 学生。
  • class: 课程名。

请编写一个解决方案，查询至少有 5 名学生 的所有课程。

解题思路​

这道题考查 基本分组 + HAVING 过滤。

分析过程：

  1. 按课程分组
  2. 统计每门课的学生数
  3. 筛选学生数 >= 5 的课程

关于去重：

题目中 (student, class) 是唯一的，所以不需要 DISTINCT。如果可能有重复选课记录，应该用 COUNT(DISTINCT student)。

SQL 实现 (MySQL)​

SELECT class 
FROM Courses 
GROUP BY class 
HAVING COUNT(*) >= 5;

1729. 求关注者的数量 (Find Followers Count)​

题目描述​

Followers 表保存了社交媒体中的关注关系：

  • user_id: 用户 ID。
  • follower_id: 关注者的 ID。

请编写一个解决方案，查询每个用户的关注者人数。结果按 user_id 升序排列。

解题思路​

这是一道基础的分组统计题。

SQL 实现 (MySQL)​

SELECT user_id, COUNT(follower_id) AS followers_count 
FROM Followers 
GROUP BY user_id 
ORDER BY user_id;

619. 只出现一次的最大数字 (Biggest Single Number)​

题目描述​

MyNumbers 表包含一组整数。 单一数字 是指在该表中只出现过一次的数字。请编写一个解决方案，找出最大的单一数字。如果不存在，则返回 null。

解题思路​

这道题考查 子查询 + 聚合函数处理空结果。

分析过程：

  1. 找出所有只出现一次的数字（分组后计数为 1）
  2. 从这些数字中找出最大值
  3. 处理"不存在"的情况（返回 NULL）

为什么需要子查询？

  • 直接用 HAVING COUNT(*) = 1 得到的是所有单一数字
  • 需要再对这些数字取 MAX()

MAX 函数的特殊行为：

  • 当输入为空集时，MAX() 返回 NULL
  • 这正好符合题目"不存在则返回 NULL"的要求

SQL 实现 (MySQL)​

SELECT MAX(num) AS num 
FROM (
    SELECT num 
    FROM MyNumbers 
    GROUP BY num 
    HAVING COUNT(*) = 1
) t;

代码解析：

  • 子查询找出所有单一数字
  • 外层 MAX() 取最大值
  • 如果没有单一数字，子查询返回空集，MAX() 返回 NULL

1045. 买下所有产品的客户 (Customers Who Bought All Products)​

题目描述​

你有两张表：

  • Customer: 包含 customer_id 和 product_key。
  • Product: 包含 product_key (主键)。记录了商店中所有可出售的产品。

请编写一个解决方案，查询买全了 Product 表中 所有产品 的客户 ID。

解题思路​

这道题考查 动态阈值比较。

核心思路：

客户购买的不同产品数 = 产品表中的产品总数

动态获取产品总数：

产品总数不是固定值，需要通过子查询获取：

SELECT COUNT(*) FROM Product

为什么要用 DISTINCT？

一个客户可能多次购买同一产品，所以需要统计"不同产品的数量"。

SQL 实现 (MySQL)​

SELECT customer_id 
FROM Customer 
GROUP BY customer_id 
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

代码解析：

  • GROUP BY customer_id：按客户分组
  • COUNT(DISTINCT product_key)：每个客户购买的不同产品数
  • (SELECT COUNT(*) FROM Product)：产品总数
  • HAVING 比较两者是否相等

知识点总结​

GROUP BY 核心要点​

  1. 分组后每组只返回一行
  2. SELECT 中的非聚合列必须出现在 GROUP BY 中
  3. WHERE 过滤行，HAVING 过滤组

常见聚合函数​

函数说明示例
COUNT(*)统计行数COUNT(*)
COUNT(column)统计非 NULL 值COUNT(bonus)
COUNT(DISTINCT column)统计不同值数量COUNT(DISTINCT user_id)
SUM(column)求和SUM(amount)
AVG(column)求平均值AVG(score)
MAX(column)最大值MAX(price)
MIN(column)最小值MIN(created_at)

常见模式​

  1. 基础分组统计：GROUP BY + COUNT/SUM/AVG
  2. 分组后过滤：GROUP BY + HAVING
  3. 与子查询比较：HAVING COUNT(...) = (SELECT ...)
  4. 范围条件：HAVING MIN(...) >= ... AND MAX(...) <= ...

注意事项​

  1. 理解 WHERE 和 HAVING 的执行时机
  2. 注意 COUNT(*) 和 COUNT(DISTINCT ...) 的区别
  3. MAX()/MIN() 在空集上返回 NULL
  4. 分组字段的选择直接影响结果的正确性