聚合排序与分页

7.9 聚合查询

7.9.1 概述

image-5acfa5bb

7.9.2 常用聚合函数

函数 说明 示例
COUNT(*) 统计行数 SELECT COUNT(*) FROM 学生
COUNT(列) 统计非NULL值的个数 SELECT COUNT(成绩) FROM 选课
SUM(列) 求和 SELECT SUM(价格) FROM 商品
AVG(列) 求平均值 SELECT AVG(成绩) FROM 选课
MAX(列) 求最大值 SELECT MAX(成绩) FROM 选课
MIN(列) 求最小值 SELECT MIN(成绩) FROM 选课

7.9.3 仅由聚合函数实现

COUNT 计数
-- 统计学生人数
SELECT COUNT(*) FROM 学生;  -- 无列名

-- 添加列别名
SELECT COUNT(*) AS 学生人数 FROM 学生;

-- 统计教师人数
SELECT COUNT(*) AS 教师人数 FROM 教师;
SUM 求和
-- 查询图书馆的图书总价值
SELECT SUM(price) AS 图书总价值 FROM books;
AVG 求平均
-- 查询图书平均价格
SELECT AVG(price) AS 图书平均价值 FROM books;
MIN / MAX 求极值
-- 查询最低价格
SELECT MIN(price) AS 图书最低价值 FROM books;

-- 查询最高价格
SELECT MAX(price) AS 图书最高价值 FROM books;

7.9.4 GROUP BY 分组查询

基本用法
-- GROUP BY 有去重效果（类似 DISTINCT）
SELECT 成绩 FROM 选课成绩 GROUP BY 成绩;

-- 多字段分组
SELECT 课程编号, 成绩 FROM 选课成绩 GROUP BY 课程编号, 成绩;

💡 GROUP BY 规则

SELECT 后面的列，必须满足以下条件之一：

  1. 出现在 GROUP BY 子句中
  2. 被聚合函数包裹
✓ SELECT 班级, COUNT(*) FROM 学生 GROUP BY 班级
✓ SELECT 班级, 性别, COUNT(*) FROM 学生 GROUP BY 班级, 性别
✗ SELECT 班级, 姓名 FROM 学生 GROUP BY 班级  -- 错误！
GROUP BY + AVG
-- 计算所有学生的总平均成绩（不分组）
SELECT AVG(成绩) AS 总平均成绩 FROM 选课成绩;

-- 计算每个学生的平均成绩（按学号分组）
SELECT 学号, AVG(成绩) AS 学生平均成绩
FROM 选课成绩
GROUP BY 学号;

-- 计算每门课程的平均成绩（按课程分组）
SELECT 课程编号, AVG(成绩) AS 课程平均成绩
FROM 选课成绩
GROUP BY 课程编号;
GROUP BY + MAX/MIN
-- 各科最高分
SELECT 课程编号, MAX(成绩) AS 最高成绩
FROM 选课成绩
GROUP BY 课程编号;

-- 各科最低分
SELECT 课程编号, MIN(成绩) AS 最低成绩
FROM 选课成绩
GROUP BY 课程编号;
GROUP BY + COUNT
-- 查询不同课堂的选课人数
SELECT 课堂编号, COUNT(*) AS 选课人数
FROM 选课成绩
GROUP BY 课堂编号;

-- 查询每个学生的选课门数
SELECT 学号, COUNT(*) AS 选课门数
FROM 选课成绩
GROUP BY 学号;

-- 查询各班级男女生人数
SELECT 专业班级, 性别, COUNT(*) AS 人数
FROM 学生
GROUP BY 专业班级, 性别;
GROUP BY + SUM
-- 按出版社分组，统计图书总价值
SELECT Publisher, SUM(price) AS 出版社图书价值总计
FROM books
GROUP BY Publisher;
GROUP BY + WHERE
-- 查询2017-2018第一学期各课堂的平均成绩
SELECT 课堂编号, AVG(成绩) AS 平均成绩
FROM 选课成绩
WHERE 课堂编号 LIKE '2017-2018-1%'
GROUP BY 课堂编号;

💡 执行顺序：WHERE 先过滤 → 再 GROUP BY 分组 → 最后聚合计算

7.9.5 HAVING 分组后过滤

WHERE vs HAVING
特性 WHERE HAVING
执行时机 分组前 分组后
能否用聚合函数 ✗ 不能 ✓ 可以
过滤对象 过滤行 过滤分组
别名使用 ✗ 不能用别名 ✓ 可以用别名
image-16c7d487
HAVING 使用示例
-- 查询平均分大于85分的课堂
SELECT 课堂编号, AVG(成绩) AS 平均成绩
FROM 选课成绩
GROUP BY 课堂编号
HAVING AVG(成绩) > 85;

-- ✗ 错误用法：WHERE 中不能使用聚合函数！
SELECT 课堂编号, AVG(成绩) AS 平均成绩
FROM 选课成绩
WHERE AVG(成绩) > 85  -- 错误！
GROUP BY 课堂编号;
HAVING 过滤空值
-- 查询去掉无职称的职称统计
-- 方法1：使用 HAVING
SELECT 职称, COUNT(*) AS 人数
FROM 教师
GROUP BY 职称
HAVING 职称 IS NOT NULL;

-- 方法2：使用 WHERE（推荐，效率更高）
SELECT 职称, COUNT(*) AS 人数
FROM 教师
WHERE 职称 IS NOT NULL
GROUP BY 职称;
综合示例
-- 选课超过两门且成绩都在80分以上的学生学号
SELECT 学号
FROM 选课成绩
WHERE 成绩 >= 80
GROUP BY 学号
HAVING COUNT(课程编号) >= 2;

-- 查询不同职称的教师人数，筛选出人数>=2的
SELECT 职称, COUNT(*) AS 人数
FROM 教师
GROUP BY 职称
HAVING COUNT(*) >= 2;

-- 统计各省份男女生人数
SELECT 籍贯,
    SUM(CASE WHEN 性别 = '男' THEN 1 ELSE 0 END) AS 男生人数,
    SUM(CASE WHEN 性别 = '女' THEN 1 ELSE 0 END) AS 女生人数
FROM 学生
GROUP BY 籍贯;

7.9.6 子查询与聚合函数结合

-- 查找高等教育出版社出版的、定价高于所有图书平均定价的图书
SELECT * FROM books
WHERE Price > (SELECT AVG(price) FROM books)
  AND Publisher = '高等教育出版社';

7.10 ORDER BY 排序

7.10.1 基本排序

-- 默认升序 ASC
SELECT 姓名, 职称 FROM 教师 ORDER BY 职称;

-- 显式升序
SELECT 姓名, 职称 FROM 教师 ORDER BY 职称 ASC;

-- 降序
SELECT 学号, 课程编号, 成绩 FROM 选课成绩 ORDER BY 成绩 DESC;

7.10.2 多列排序

-- 先按课程排序，课程相同按成绩降序
SELECT * FROM 选课成绩
ORDER BY 课程编号, 成绩 DESC;

7.10.3 自定义排序

-- 按职称高低排序（使用 CASE WHEN）
SELECT 姓名, 职称 FROM 教师
ORDER BY
    CASE
        WHEN 职称 = '教授' THEN 1
        WHEN 职称 = '副教授' THEN 2
        WHEN 职称 = '讲师' THEN 3
        WHEN 职称 = '助教' THEN 4
        ELSE 5
    END;

-- MySQL 特有：使用 FIELD 函数
SELECT 姓名, 职称 FROM 教师
ORDER BY FIELD(职称, '教授', '副教授', '讲师', '助教');

7.11 LIMIT 子句

7.11.1 TOP vs LIMIT

数据库 语法
SQL Server SELECT TOP(5) * FROM 表 ORDER BY 列
MySQL SELECT * FROM 表 ORDER BY 列 LIMIT 5

⚠️ 注意：LIMIT 放在语句最后！

7.11.2 基本用法

-- 查询成绩前5名
SELECT 学号, 课程编号, 成绩
FROM 选课成绩
ORDER BY 成绩 DESC
LIMIT 5;

7.11.3 分页查询

-- LIMIT offset, count
-- offset：跳过的行数（从0开始）
-- count：返回的行数

-- 第1页（前10条）
SELECT * FROM 学生 LIMIT 0, 10;
-- 或
SELECT * FROM 学生 LIMIT 10;

-- 第2页（第11-20条）
SELECT * FROM 学生 LIMIT 10, 10;

-- 第3页（第21-30条）
SELECT * FROM 学生 LIMIT 20, 10;

-- 通用公式：第 n 页，每页 size 条
-- LIMIT (n-1)*size, size

7.11.4 百分比查询

-- MySQL 没有 TOP PERCENT，需要使用子查询

-- 查询前30%的成绩（方法1：使用变量）
SET @total = (SELECT COUNT(*) FROM 选课成绩);
SET @limit_num = CEIL(@total * 0.3);

-- 查询前30%的成绩（方法2：使用窗口函数，MySQL 8.0+）
SELECT 学号, 课程编号, 成绩
FROM (
    SELECT *,
           PERCENT_RANK() OVER (ORDER BY 成绩 DESC) AS pct
    FROM 选课成绩
) t
WHERE pct <= 0.3;

7.11.5 WITH TIES 的替代方案

-- SQL Server: TOP(5) WITH TIES 会包含所有与第5名成绩相同的记录
-- MySQL 需要子查询实现

-- 查询成绩前5名（包含并列）
SELECT 学号, 课程编号, 成绩
FROM 选课成绩
WHERE 成绩 >= (
    SELECT DISTINCT 成绩
    FROM 选课成绩
    ORDER BY 成绩 DESC
    LIMIT 4, 1  -- 第5名的成绩
)
ORDER BY 成绩 DESC;

7.11.6 注意事项

-- ⚠️ 必须先 ORDER BY 才有意义
SELECT 学号, 课程编号, 成绩 FROM 选课成绩 LIMIT 5;
-- 只是返回表中前5行，不是真正的"前5名"！

-- ✓ 正确写法：先排序再取前N
SELECT 学号, 课程编号, 成绩
FROM 选课成绩
ORDER BY 成绩 DESC
LIMIT 5;

⬅️ 单表与条件查询 🏠 00-数据库 ➡️ 多表查询与集合运算