---
title: "11-聚合排序与分页"
created: 2026-01-07
tags:
  - 项目筑基
---

# 聚合排序与分页

### **7.9 聚合查询**

#### **7.9.1 概述**

![[image-5acfa5bb.png]]

#### **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 计数**

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

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

-- 统计教师人数
SELECT COUNT(*) AS 教师人数 FROM 教师;
```

##### **SUM 求和**

```sql
-- 查询图书馆的图书总价值
SELECT SUM(price) AS 图书总价值 FROM books;
```

##### **AVG 求平均**

```sql
-- 查询图书平均价格
SELECT AVG(price) AS 图书平均价值 FROM books;
```

##### **MIN / MAX 求极值**

```sql
-- 查询最低价格
SELECT MIN(price) AS 图书最低价值 FROM books;

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

#### **7.9.4 GROUP BY 分组查询**

##### **基本用法**

```sql
-- 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**

```sql
-- 计算所有学生的总平均成绩（不分组）
SELECT AVG(成绩) AS 总平均成绩 FROM 选课成绩;

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

-- 计算每门课程的平均成绩（按课程分组）
SELECT 课程编号, AVG(成绩) AS 课程平均成绩
FROM 选课成绩
GROUP BY 课程编号;
```

##### **GROUP BY + MAX/MIN**

```sql
-- 各科最高分
SELECT 课程编号, MAX(成绩) AS 最高成绩
FROM 选课成绩
GROUP BY 课程编号;

-- 各科最低分
SELECT 课程编号, MIN(成绩) AS 最低成绩
FROM 选课成绩
GROUP BY 课程编号;
```

##### **GROUP BY + COUNT**

```sql
-- 查询不同课堂的选课人数
SELECT 课堂编号, COUNT(*) AS 选课人数
FROM 选课成绩
GROUP BY 课堂编号;

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

-- 查询各班级男女生人数
SELECT 专业班级, 性别, COUNT(*) AS 人数
FROM 学生
GROUP BY 专业班级, 性别;
```

##### **GROUP BY + SUM**

```sql
-- 按出版社分组，统计图书总价值
SELECT Publisher, SUM(price) AS 出版社图书价值总计
FROM books
GROUP BY Publisher;
```

##### **GROUP BY + WHERE**

```sql
-- 查询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.png]]

##### **HAVING 使用示例**

```sql
-- 查询平均分大于85分的课堂
SELECT 课堂编号, AVG(成绩) AS 平均成绩
FROM 选课成绩
GROUP BY 课堂编号
HAVING AVG(成绩) > 85;

-- ✗ 错误用法：WHERE 中不能使用聚合函数！
SELECT 课堂编号, AVG(成绩) AS 平均成绩
FROM 选课成绩
WHERE AVG(成绩) > 85  -- 错误！
GROUP BY 课堂编号;
```

##### **HAVING 过滤空值**

```sql
-- 查询去掉无职称的职称统计
-- 方法1：使用 HAVING
SELECT 职称, COUNT(*) AS 人数
FROM 教师
GROUP BY 职称
HAVING 职称 IS NOT NULL;

-- 方法2：使用 WHERE（推荐，效率更高）
SELECT 职称, COUNT(*) AS 人数
FROM 教师
WHERE 职称 IS NOT NULL
GROUP BY 职称;
```

##### **综合示例**

```sql
-- 选课超过两门且成绩都在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 子查询与聚合函数结合**

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

### **7.10 ORDER BY 排序**

#### **7.10.1 基本排序**

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

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

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

#### **7.10.2 多列排序**

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

#### **7.10.3 自定义排序**

```sql
-- 按职称高低排序（使用 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 基本用法**

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

#### **7.11.3 分页查询**

```sql
-- 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 百分比查询**

```sql
-- 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
-- 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 注意事项**

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

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

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