769 lines
22 KiB
Markdown
769 lines
22 KiB
Markdown
# MySQL ORDER BY 语法使用文档
|
||
|
||
## 目录
|
||
1. [ORDER BY 基础语法](#order-by-基础语法)
|
||
2. [ORDER BY 规则和特性](#order-by-规则和特性)
|
||
3. [示例数据准备](#示例数据准备)
|
||
4. [基础排序示例](#基础排序示例)
|
||
5. [多列排序](#多列排序)
|
||
6. [特殊排序场景](#特殊排序场景)
|
||
7. [复杂排序查询](#复杂排序查询)
|
||
8. [面试题和实际案例](#面试题和实际案例)
|
||
9. [性能优化和最佳实践](#性能优化和最佳实践)
|
||
|
||
## ORDER BY 基础语法
|
||
|
||
ORDER BY 用于对查询结果进行排序。
|
||
|
||
```sql
|
||
SELECT column1, column2, ...
|
||
FROM table_name
|
||
[WHERE condition]
|
||
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
|
||
[LIMIT number];
|
||
```
|
||
|
||
**关键字说明：**
|
||
- `ASC`：升序排列（默认）
|
||
- `DESC`：降序排列
|
||
- 可以指定多个排序列
|
||
- NULL 值默认排在最前面（ASC）或最后面（DESC）
|
||
|
||
## ORDER BY 规则和特性
|
||
|
||
1. **排序优先级**：从左到右依次排序
|
||
2. **数据类型排序**：
|
||
- 数字：按数值大小
|
||
- 字符串：按字典序（字母顺序）
|
||
- 日期：按时间先后
|
||
- NULL：默认最小值处理
|
||
3. **性能影响**：ORDER BY 可能触发文件排序，影响查询性能
|
||
4. **与 LIMIT 结合**：获取排序后的前 N 条记录
|
||
|
||
## 示例数据准备
|
||
|
||
### 创建示例表
|
||
|
||
```sql
|
||
-- 学生表
|
||
CREATE TABLE students (
|
||
id INT PRIMARY KEY AUTO_INCREMENT,
|
||
name VARCHAR(50) NOT NULL,
|
||
age INT,
|
||
gender ENUM('男', '女'),
|
||
class_id INT,
|
||
score DECIMAL(5,2),
|
||
enrollment_date DATE,
|
||
city VARCHAR(30)
|
||
);
|
||
|
||
-- 订单表
|
||
CREATE TABLE orders (
|
||
order_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
customer_name VARCHAR(50),
|
||
product_name VARCHAR(100),
|
||
order_date DATETIME,
|
||
amount DECIMAL(10,2),
|
||
status ENUM('pending', 'processing', 'completed', 'cancelled'),
|
||
priority INT,
|
||
region VARCHAR(30)
|
||
);
|
||
|
||
-- 员工表
|
||
CREATE TABLE staff (
|
||
emp_id INT PRIMARY KEY,
|
||
name VARCHAR(50),
|
||
department VARCHAR(30),
|
||
salary DECIMAL(10,2),
|
||
hire_date DATE,
|
||
manager_id INT,
|
||
performance_score DECIMAL(3,1),
|
||
bonus DECIMAL(8,2)
|
||
);
|
||
|
||
-- 产品销售表
|
||
CREATE TABLE product_sales (
|
||
id INT PRIMARY KEY AUTO_INCREMENT,
|
||
product_id INT,
|
||
product_name VARCHAR(100),
|
||
category VARCHAR(50),
|
||
sale_date DATE,
|
||
quantity INT,
|
||
unit_price DECIMAL(8,2),
|
||
discount_rate DECIMAL(3,2),
|
||
sales_rep VARCHAR(50)
|
||
);
|
||
```
|
||
|
||
### 插入示例数据
|
||
|
||
```sql
|
||
-- 插入学生数据
|
||
INSERT INTO students (name, age, gender, class_id, score, enrollment_date, city) VALUES
|
||
('张三', 20, '男', 1, 85.5, '2023-09-01', '北京'),
|
||
('李四', 19, '女', 1, 92.0, '2023-09-01', '上海'),
|
||
('王五', 21, '男', 2, 78.5, '2023-09-01', '深圳'),
|
||
('赵六', 20, '女', 2, 88.0, '2023-09-01', '广州'),
|
||
('钱七', 22, '男', 1, 76.0, '2022-09-01', '北京'),
|
||
('孙八', 19, '女', 3, 95.5, '2023-09-01', '杭州'),
|
||
('周九', 20, '男', 3, 82.0, '2023-09-01', '成都'),
|
||
('吴十', 21, '女', 2, 89.5, '2022-09-01', '南京'),
|
||
('郑一', 19, '男', 1, 91.0, '2023-09-01', '西安'),
|
||
('王二', NULL, '女', 3, 87.0, '2023-09-01', '重庆');
|
||
|
||
-- 插入订单数据
|
||
INSERT INTO orders (customer_name, product_name, order_date, amount, status, priority, region) VALUES
|
||
('客户A', '笔记本电脑', '2024-01-15 10:30:00', 5999.00, 'completed', 1, '华北'),
|
||
('客户B', '手机', '2024-01-16 14:20:00', 3999.00, 'processing', 2, '华东'),
|
||
('客户C', '平板电脑', '2024-01-16 16:45:00', 2999.00, 'pending', 3, '华南'),
|
||
('客户A', '耳机', '2024-01-17 09:15:00', 299.00, 'completed', 2, '华北'),
|
||
('客户D', '键盘', '2024-01-17 11:30:00', 199.00, 'cancelled', 3, '华西'),
|
||
('客户E', '鼠标', '2024-01-18 13:40:00', 99.00, 'completed', 1, '华东'),
|
||
('客户B', '显示器', '2024-01-18 15:20:00', 1299.00, 'processing', 1, '华东'),
|
||
('客户F', '音响', '2024-01-19 08:50:00', 899.00, 'pending', 2, '华南'),
|
||
('客户C', '摄像头', '2024-01-19 12:10:00', 399.00, 'completed', 3, '华南'),
|
||
('客户G', '打印机', '2024-01-20 16:30:00', 1599.00, 'processing', 1, '华北');
|
||
|
||
-- 插入员工数据
|
||
INSERT INTO staff (emp_id, name, department, salary, hire_date, manager_id, performance_score, bonus) VALUES
|
||
(1001, '张经理', '技术部', 15000.00, '2020-03-15', NULL, 9.2, 5000.00),
|
||
(1002, '李工程师', '技术部', 12000.00, '2021-05-20', 1001, 8.8, 3000.00),
|
||
(1003, '王设计师', '设计部', 10000.00, '2021-08-10', NULL, 8.5, 2500.00),
|
||
(1004, '赵分析师', '数据部', 11000.00, '2022-01-15', NULL, 9.0, 3500.00),
|
||
(1005, '钱开发', '技术部', 9000.00, '2022-04-20', 1001, 7.8, 2000.00),
|
||
(1006, '孙测试', '技术部', 8500.00, '2022-07-01', 1001, 8.2, 1500.00),
|
||
(1007, '周产品', '产品部', 13000.00, '2021-12-05', NULL, 8.9, 4000.00),
|
||
(1008, '吴运营', '运营部', 8000.00, '2023-02-10', NULL, 7.5, 1000.00),
|
||
(1009, '郑销售', '销售部', 7500.00, '2023-03-20', NULL, 8.1, 1800.00),
|
||
(1010, '刘助理', '技术部', 6000.00, '2023-09-01', 1002, NULL, 500.00);
|
||
|
||
-- 插入产品销售数据
|
||
INSERT INTO product_sales (product_id, product_name, category, sale_date, quantity, unit_price, discount_rate, sales_rep) VALUES
|
||
(101, 'iPhone 15', '手机', '2024-01-10', 50, 5999.00, 0.05, '张销售'),
|
||
(102, '华为Mate60', '手机', '2024-01-10', 30, 4999.00, 0.10, '李销售'),
|
||
(103, 'MacBook Pro', '笔记本', '2024-01-11', 20, 12999.00, 0.03, '张销售'),
|
||
(104, '联想ThinkPad', '笔记本', '2024-01-11', 40, 6999.00, 0.08, '王销售'),
|
||
(105, 'iPad Air', '平板', '2024-01-12', 25, 3999.00, 0.06, '李销售'),
|
||
(106, '小米平板', '平板', '2024-01-12', 35, 1999.00, 0.12, '赵销售'),
|
||
(107, 'AirPods Pro', '耳机', '2024-01-13', 100, 1999.00, 0.08, '张销售'),
|
||
(108, '索尼耳机', '耳机', '2024-01-13', 60, 2999.00, 0.15, '王销售'),
|
||
(109, 'Dell显示器', '显示器', '2024-01-14', 80, 1599.00, 0.10, '李销售'),
|
||
(110, '三星显示器', '显示器', '2024-01-14', 45, 2299.00, 0.07, '赵销售');
|
||
```
|
||
|
||
## 基础排序示例
|
||
|
||
### 1. 单列升序排序（默认）
|
||
|
||
```sql
|
||
-- 按年龄升序排列学生
|
||
SELECT name, age FROM students ORDER BY age;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+------+
|
||
| name | age |
|
||
+------+------+
|
||
| 王二 | NULL |
|
||
| 李四 | 19 |
|
||
| 孙八 | 19 |
|
||
| 郑一 | 19 |
|
||
| 张三 | 20 |
|
||
| 赵六 | 20 |
|
||
| 周九 | 20 |
|
||
| 王五 | 21 |
|
||
| 吴十 | 21 |
|
||
| 钱七 | 22 |
|
||
+------+------+
|
||
```
|
||
|
||
### 2. 单列降序排序
|
||
|
||
```sql
|
||
-- 按分数降序排列学生
|
||
SELECT name, score FROM students ORDER BY score DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+-------+
|
||
| name | score |
|
||
+------+-------+
|
||
| 孙八 | 95.50 |
|
||
| 李四 | 92.00 |
|
||
| 郑一 | 91.00 |
|
||
| 吴十 | 89.50 |
|
||
| 赵六 | 88.00 |
|
||
| 王二 | 87.00 |
|
||
| 张三 | 85.50 |
|
||
| 周九 | 82.00 |
|
||
| 王五 | 78.50 |
|
||
| 钱七 | 76.00 |
|
||
+------+-------+
|
||
```
|
||
|
||
### 3. 字符串排序
|
||
|
||
```sql
|
||
-- 按姓名字母顺序排列
|
||
SELECT name, age FROM students ORDER BY name;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+------+
|
||
| name | age |
|
||
+------+------+
|
||
| 钱七 | 22 |
|
||
| 孙八 | 19 |
|
||
| 王二 | NULL |
|
||
| 王五 | 21 |
|
||
| 吴十 | 21 |
|
||
| 张三 | 20 |
|
||
| 赵六 | 20 |
|
||
| 郑一 | 19 |
|
||
| 周九 | 20 |
|
||
| 李四 | 19 |
|
||
+------+-------+
|
||
```
|
||
|
||
### 4. 日期排序
|
||
|
||
```sql
|
||
-- 按入学日期排序
|
||
SELECT name, enrollment_date FROM students ORDER BY enrollment_date;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+-----------------+
|
||
| name | enrollment_date |
|
||
+------+-----------------+
|
||
| 钱七 | 2022-09-01 |
|
||
| 吴十 | 2022-09-01 |
|
||
| 张三 | 2023-09-01 |
|
||
| 李四 | 2023-09-01 |
|
||
| 王五 | 2023-09-01 |
|
||
| 赵六 | 2023-09-01 |
|
||
| 孙八 | 2023-09-01 |
|
||
| 周九 | 2023-09-01 |
|
||
| 郑一 | 2023-09-01 |
|
||
| 王二 | 2023-09-01 |
|
||
+------+-----------------+
|
||
```
|
||
|
||
## 多列排序
|
||
|
||
### 1. 多列升序排序
|
||
|
||
```sql
|
||
-- 先按班级排序，再按分数排序
|
||
SELECT name, class_id, score
|
||
FROM students
|
||
ORDER BY class_id, score;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+----------+-------+
|
||
| name | class_id | score |
|
||
+------+----------+-------+
|
||
| 钱七 | 1 | 76.00 |
|
||
| 张三 | 1 | 85.50 |
|
||
| 郑一 | 1 | 91.00 |
|
||
| 李四 | 1 | 92.00 |
|
||
| 王五 | 2 | 78.50 |
|
||
| 赵六 | 2 | 88.00 |
|
||
| 吴十 | 2 | 89.50 |
|
||
| 周九 | 3 | 82.00 |
|
||
| 王二 | 3 | 87.00 |
|
||
| 孙八 | 3 | 95.50 |
|
||
+------+----------+-------+
|
||
```
|
||
|
||
### 2. 混合排序（升序+降序）
|
||
|
||
```sql
|
||
-- 按班级升序，分数降序
|
||
SELECT name, class_id, score
|
||
FROM students
|
||
ORDER BY class_id ASC, score DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------+----------+-------+
|
||
| name | class_id | score |
|
||
+------+----------+-------+
|
||
| 李四 | 1 | 92.00 |
|
||
| 郑一 | 1 | 91.00 |
|
||
| 张三 | 1 | 85.50 |
|
||
| 钱七 | 1 | 76.00 |
|
||
| 吴十 | 2 | 89.50 |
|
||
| 赵六 | 2 | 88.00 |
|
||
| 王五 | 2 | 78.50 |
|
||
| 孙八 | 3 | 95.50 |
|
||
| 王二 | 3 | 87.00 |
|
||
| 周九 | 3 | 82.00 |
|
||
+------+----------+-------+
|
||
```
|
||
|
||
### 3. 三列排序
|
||
|
||
```sql
|
||
-- 按部门、薪资、绩效排序
|
||
SELECT name, department, salary, performance_score
|
||
FROM staff
|
||
ORDER BY department, salary DESC, performance_score DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+----------+----------+----------+------------------+
|
||
| name | department | salary | performance_score |
|
||
+----------+----------+----------+------------------+
|
||
| 王设计师 | 设计部 | 10000.00 | 8.50 |
|
||
| 赵分析师 | 数据部 | 11000.00 | 9.00 |
|
||
| 周产品 | 产品部 | 13000.00 | 8.90 |
|
||
| 张经理 | 技术部 | 15000.00 | 9.20 |
|
||
| 李工程师 | 技术部 | 12000.00 | 8.80 |
|
||
| 钱开发 | 技术部 | 9000.00 | 7.80 |
|
||
| 孙测试 | 技术部 | 8500.00 | 8.20 |
|
||
| 刘助理 | 技术部 | 6000.00 | NULL |
|
||
| 吴运营 | 运营部 | 8000.00 | 7.50 |
|
||
| 郑销售 | 销售部 | 7500.00 | 8.10 |
|
||
+----------+----------+----------+------------------+
|
||
```
|
||
|
||
## 特殊排序场景
|
||
|
||
### 1. NULL 值处理
|
||
|
||
```sql
|
||
-- 查看NULL值的排序位置
|
||
SELECT name, age FROM students ORDER BY age;
|
||
|
||
-- 使用 ISNULL() 函数控制NULL值排序
|
||
SELECT name, age
|
||
FROM students
|
||
ORDER BY ISNULL(age), age; -- NULL值排在最后
|
||
```
|
||
|
||
### 2. 自定义排序（FIELD函数）
|
||
|
||
```sql
|
||
-- 按自定义状态优先级排序
|
||
SELECT customer_name, status, amount
|
||
FROM orders
|
||
ORDER BY FIELD(status, 'pending', 'processing', 'completed', 'cancelled'), amount DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+------------+------------+---------+
|
||
| customer_name | status | amount |
|
||
+------------+------------+---------+
|
||
| 客户C | pending | 2999.00 |
|
||
| 客户F | pending | 899.00 |
|
||
| 客户B | processing | 3999.00 |
|
||
| 客户G | processing | 1599.00 |
|
||
| 客户B | processing | 1299.00 |
|
||
| 客户A | completed | 5999.00 |
|
||
| 客户F | completed | 899.00 |
|
||
| 客户C | completed | 399.00 |
|
||
| 客户A | completed | 299.00 |
|
||
| 客户E | completed | 99.00 |
|
||
| 客户D | cancelled | 199.00 |
|
||
+------------+------------+---------+
|
||
```
|
||
|
||
### 3. 条件排序（CASE WHEN）
|
||
|
||
```sql
|
||
-- 根据不同条件进行排序
|
||
SELECT name, department, salary,
|
||
CASE
|
||
WHEN department = '技术部' THEN 1
|
||
WHEN department = '产品部' THEN 2
|
||
WHEN department = '设计部' THEN 3
|
||
ELSE 4
|
||
END AS dept_priority
|
||
FROM staff
|
||
ORDER BY dept_priority, salary DESC;
|
||
```
|
||
|
||
### 4. 计算字段排序
|
||
|
||
```sql
|
||
-- 按销售额排序（数量×单价×(1-折扣率)）
|
||
SELECT product_name, quantity, unit_price, discount_rate,
|
||
ROUND(quantity * unit_price * (1 - discount_rate), 2) AS total_sales
|
||
FROM product_sales
|
||
ORDER BY total_sales DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+-------------+----------+------------+---------------+-------------+
|
||
| product_name | quantity | unit_price | discount_rate | total_sales |
|
||
+-------------+----------+------------+---------------+-------------+
|
||
| iPhone 15 | 50 | 5999.00 | 0.05 | 284952.50 |
|
||
| MacBook Pro | 20 | 12999.00 | 0.03 | 251980.60 |
|
||
| 联想ThinkPad | 40 | 6999.00 | 0.08 | 257569.60 |
|
||
| AirPods Pro | 100 | 1999.00 | 0.08 | 183908.00 |
|
||
| 索尼耳机 | 60 | 2999.00 | 0.15 | 152949.00 |
|
||
| 华为Mate60 | 30 | 4999.00 | 0.10 | 134973.00 |
|
||
| Dell显示器 | 80 | 1599.00 | 0.10 | 115128.00 |
|
||
| iPad Air | 25 | 3999.00 | 0.06 | 93975.00 |
|
||
| 三星显示器 | 45 | 2299.00 | 0.07 | 96218.55 |
|
||
| 小米平板 | 35 | 1999.00 | 0.12 | 61652.40 |
|
||
+-------------+----------+------------+---------------+-------------+
|
||
```
|
||
|
||
## 复杂排序查询
|
||
|
||
### 1. 分组后排序
|
||
|
||
```sql
|
||
-- 各部门平均薪资，按平均薪资降序
|
||
SELECT department,
|
||
COUNT(*) AS emp_count,
|
||
ROUND(AVG(salary), 2) AS avg_salary
|
||
FROM staff
|
||
GROUP BY department
|
||
ORDER BY avg_salary DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+----------+-----------+------------+
|
||
| department | emp_count | avg_salary |
|
||
+----------+-----------+------------+
|
||
| 技术部 | 5 | 10100.00 |
|
||
| 产品部 | 1 | 13000.00 |
|
||
| 数据部 | 1 | 11000.00 |
|
||
| 设计部 | 1 | 10000.00 |
|
||
| 运营部 | 1 | 8000.00 |
|
||
| 销售部 | 1 | 7500.00 |
|
||
+----------+-----------+------------+
|
||
```
|
||
|
||
### 2. 子查询结果排序
|
||
|
||
```sql
|
||
-- 查找每个班级分数最高的学生
|
||
SELECT s1.name, s1.class_id, s1.score
|
||
FROM students s1
|
||
WHERE s1.score = (
|
||
SELECT MAX(s2.score)
|
||
FROM students s2
|
||
WHERE s2.class_id = s1.class_id
|
||
)
|
||
ORDER BY s1.class_id, s1.score DESC;
|
||
```
|
||
|
||
### 3. 窗口函数排序
|
||
|
||
```sql
|
||
-- 使用ROW_NUMBER()进行排名
|
||
SELECT name, department, salary,
|
||
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
|
||
ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank
|
||
FROM staff
|
||
ORDER BY department, dept_rank;
|
||
```
|
||
|
||
### 4. 复杂条件排序
|
||
|
||
```sql
|
||
-- 优先显示高优先级且金额大的订单
|
||
SELECT customer_name, product_name, amount, priority, status,
|
||
CASE
|
||
WHEN priority = 1 AND amount > 1000 THEN 1
|
||
WHEN priority = 1 THEN 2
|
||
WHEN priority = 2 AND amount > 1000 THEN 3
|
||
WHEN priority = 2 THEN 4
|
||
ELSE 5
|
||
END AS sort_priority
|
||
FROM orders
|
||
ORDER BY sort_priority, amount DESC;
|
||
```
|
||
|
||
## 面试题和实际案例
|
||
|
||
### 面试题1：销售排名问题
|
||
|
||
**题目**：查询每个销售代表的销售总额，并按销售额降序排列，同时显示排名。
|
||
|
||
```sql
|
||
-- 解答
|
||
SELECT sales_rep,
|
||
COUNT(*) AS product_count,
|
||
SUM(quantity * unit_price * (1 - discount_rate)) AS total_sales,
|
||
RANK() OVER (ORDER BY SUM(quantity * unit_price * (1 - discount_rate)) DESC) AS sales_rank
|
||
FROM product_sales
|
||
GROUP BY sales_rep
|
||
ORDER BY total_sales DESC;
|
||
```
|
||
|
||
**结果：**
|
||
```
|
||
+----------+---------------+-------------+------------+
|
||
| sales_rep | product_count | total_sales | sales_rank |
|
||
+----------+---------------+-------------+------------+
|
||
| 张销售 | 3 | 720841.10 | 1 |
|
||
| 李销售 | 3 | 344076.00 | 2 |
|
||
| 王销售 | 2 | 410518.20 | 3 |
|
||
| 赵销售 | 2 | 157870.95 | 4 |
|
||
+----------+---------------+-------------+------------+
|
||
```
|
||
|
||
### 面试题2：分页查询优化
|
||
|
||
**题目**：实现高效的分页查询，按ID排序。
|
||
|
||
```sql
|
||
-- 传统分页（性能较差）
|
||
SELECT * FROM orders ORDER BY order_id LIMIT 1000000, 10;
|
||
|
||
-- 优化后的分页（使用索引）
|
||
SELECT o.* FROM orders o
|
||
WHERE o.order_id > (
|
||
SELECT order_id FROM orders ORDER BY order_id LIMIT 999999, 1
|
||
)
|
||
ORDER BY o.order_id LIMIT 10;
|
||
|
||
-- 更好的分页方案（记住上次的最后一条记录ID）
|
||
SELECT * FROM orders
|
||
WHERE order_id > 1000000 -- 上次查询的最后一个ID
|
||
ORDER BY order_id LIMIT 10;
|
||
```
|
||
|
||
### 面试题3：Top-N问题
|
||
|
||
**题目**：查询每个类别销售额前2名的产品。
|
||
|
||
```sql
|
||
-- 使用窗口函数解决
|
||
SELECT category, product_name, total_sales, category_rank
|
||
FROM (
|
||
SELECT category, product_name,
|
||
quantity * unit_price * (1 - discount_rate) AS total_sales,
|
||
ROW_NUMBER() OVER (PARTITION BY category ORDER BY quantity * unit_price * (1 - discount_rate) DESC) AS category_rank
|
||
FROM product_sales
|
||
) ranked_sales
|
||
WHERE category_rank <= 2
|
||
ORDER BY category, category_rank;
|
||
```
|
||
|
||
### 实际案例1：电商订单分析
|
||
|
||
**场景**：电商平台需要分析订单数据，按多个维度排序。
|
||
|
||
```sql
|
||
-- 复合排序：紧急订单优先，然后按金额降序，最后按时间升序
|
||
SELECT customer_name, product_name, amount, status, priority, order_date,
|
||
CASE
|
||
WHEN status = 'pending' AND priority = 1 THEN '紧急待处理'
|
||
WHEN status = 'processing' AND priority <= 2 THEN '优先处理中'
|
||
WHEN status = 'completed' THEN '已完成'
|
||
ELSE '普通订单'
|
||
END AS order_category
|
||
FROM orders
|
||
ORDER BY
|
||
CASE WHEN status = 'pending' AND priority = 1 THEN 1
|
||
WHEN status = 'processing' AND priority <= 2 THEN 2
|
||
WHEN status = 'pending' THEN 3
|
||
WHEN status = 'processing' THEN 4
|
||
WHEN status = 'completed' THEN 5
|
||
ELSE 6 END,
|
||
amount DESC,
|
||
order_date ASC;
|
||
```
|
||
|
||
### 实际案例2：学生成绩分析
|
||
|
||
**场景**：学校需要按多个条件对学生排序。
|
||
|
||
```sql
|
||
-- 综合排序：优秀学生优先（分数>90），然后按班级、分数排序
|
||
SELECT name, class_id, score, age,
|
||
CASE
|
||
WHEN score >= 90 THEN '优秀'
|
||
WHEN score >= 80 THEN '良好'
|
||
WHEN score >= 70 THEN '中等'
|
||
ELSE '待提高'
|
||
END AS grade_level
|
||
FROM students
|
||
ORDER BY
|
||
CASE WHEN score >= 90 THEN 1 ELSE 2 END, -- 优秀学生优先
|
||
class_id, -- 再按班级
|
||
score DESC, -- 最后按分数降序
|
||
age; -- 分数相同时按年龄升序
|
||
```
|
||
|
||
### 实际案例3：员工绩效排序
|
||
|
||
**场景**：HR部门需要按绩效和多个因素对员工排序。
|
||
|
||
```sql
|
||
-- 复杂绩效排序
|
||
SELECT name, department, salary, performance_score, bonus, hire_date,
|
||
CASE
|
||
WHEN performance_score >= 9.0 THEN 'A'
|
||
WHEN performance_score >= 8.0 THEN 'B'
|
||
WHEN performance_score >= 7.0 THEN 'C'
|
||
ELSE 'D'
|
||
END AS performance_grade
|
||
FROM staff
|
||
WHERE performance_score IS NOT NULL
|
||
ORDER BY
|
||
performance_score DESC, -- 绩效评分降序
|
||
CASE WHEN department = '技术部' THEN 1 -- 技术部优先
|
||
WHEN department = '产品部' THEN 2
|
||
ELSE 3 END,
|
||
salary DESC, -- 薪资降序
|
||
hire_date; -- 入职时间升序（资历）
|
||
```
|
||
|
||
### 面试题4：连续排名问题
|
||
|
||
**题目**：为员工按薪资排序，要求排名连续（即使薪资相同）。
|
||
|
||
```sql
|
||
-- 使用不同的排名函数
|
||
SELECT name, salary,
|
||
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, -- 连续排名
|
||
RANK() OVER (ORDER BY salary DESC) AS rank_num, -- 跳跃排名
|
||
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank -- 密集排名
|
||
FROM staff
|
||
ORDER BY salary DESC, name;
|
||
```
|
||
|
||
### 面试题5：移动平均排序
|
||
|
||
**题目**：计算每个学生的班级排名和移动平均分。
|
||
|
||
```sql
|
||
-- 窗口函数实现移动平均
|
||
SELECT name, class_id, score,
|
||
ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS class_rank,
|
||
ROUND(AVG(score) OVER (
|
||
PARTITION BY class_id
|
||
ORDER BY score DESC
|
||
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
|
||
), 2) AS moving_avg
|
||
FROM students
|
||
WHERE score IS NOT NULL
|
||
ORDER BY class_id, class_rank;
|
||
```
|
||
|
||
## 性能优化和最佳实践
|
||
|
||
### 1. 索引优化
|
||
|
||
```sql
|
||
-- 为经常排序的列创建索引
|
||
CREATE INDEX idx_students_score ON students(score);
|
||
CREATE INDEX idx_orders_date_amount ON orders(order_date, amount);
|
||
CREATE INDEX idx_staff_dept_salary ON staff(department, salary DESC);
|
||
|
||
-- 复合索引的排序优化
|
||
CREATE INDEX idx_orders_status_priority_amount ON orders(status, priority, amount DESC);
|
||
```
|
||
|
||
### 2. LIMIT 与 ORDER BY 结合使用
|
||
|
||
```sql
|
||
-- ✅ 推荐：只获取需要的记录数
|
||
SELECT name, score FROM students ORDER BY score DESC LIMIT 10;
|
||
|
||
-- ❌ 避免：不必要的全表排序
|
||
SELECT name, score FROM students ORDER BY score DESC; -- 然后在应用层取前10条
|
||
```
|
||
|
||
### 3. 避免文件排序
|
||
|
||
```sql
|
||
-- ✅ 使用索引排序（Using index）
|
||
EXPLAIN SELECT name, score FROM students ORDER BY score DESC;
|
||
|
||
-- 查看执行计划中是否出现 "Using filesort"
|
||
-- 出现则表示需要文件排序，性能较差
|
||
```
|
||
|
||
### 4. 排序字段选择
|
||
|
||
```sql
|
||
-- ✅ 推荐：使用数值型字段排序
|
||
SELECT * FROM orders ORDER BY amount DESC;
|
||
|
||
-- ⚠️ 注意：字符串排序相对较慢
|
||
SELECT * FROM orders ORDER BY customer_name;
|
||
|
||
-- ✅ 优化：为常用字符串排序创建专门索引
|
||
CREATE INDEX idx_orders_customer_name ON orders(customer_name);
|
||
```
|
||
|
||
### 5. 内存使用优化
|
||
|
||
```sql
|
||
-- 调整排序缓冲区大小（根据实际情况）
|
||
SET SESSION sort_buffer_size = 2097152; -- 2MB
|
||
|
||
-- 监控排序性能
|
||
SHOW STATUS LIKE 'Sort%';
|
||
```
|
||
|
||
### 6. 常见性能陷阱
|
||
|
||
```sql
|
||
-- ❌ 避免：在ORDER BY中使用函数
|
||
SELECT * FROM students ORDER BY UPPER(name);
|
||
|
||
-- ✅ 推荐：创建函数索引或使用计算列
|
||
ALTER TABLE students ADD COLUMN name_upper VARCHAR(50) GENERATED ALWAYS AS (UPPER(name));
|
||
CREATE INDEX idx_students_name_upper ON students(name_upper);
|
||
|
||
-- ❌ 避免：复杂的CASE WHEN排序
|
||
SELECT * FROM orders
|
||
ORDER BY CASE WHEN status = 'pending' THEN 1
|
||
WHEN status = 'processing' THEN 2
|
||
ELSE 3 END, amount DESC;
|
||
|
||
-- ✅ 推荐：添加排序辅助列
|
||
ALTER TABLE orders ADD COLUMN status_sort_order INT;
|
||
UPDATE orders SET status_sort_order = CASE
|
||
WHEN status = 'pending' THEN 1
|
||
WHEN status = 'processing' THEN 2
|
||
ELSE 3 END;
|
||
CREATE INDEX idx_orders_status_sort ON orders(status_sort_order, amount DESC);
|
||
```
|
||
|
||
### 7. 监控和调优
|
||
|
||
```sql
|
||
-- 查看慢查询日志中的排序相关查询
|
||
SHOW VARIABLES LIKE 'slow_query_log%';
|
||
|
||
-- 分析排序操作的性能
|
||
SELECT
|
||
sql_text,
|
||
exec_count,
|
||
avg_timer_wait/1000000000000 as 'avg_time_sec'
|
||
FROM performance_schema.events_statements_summary_by_digest
|
||
WHERE sql_text LIKE '%ORDER BY%'
|
||
ORDER BY avg_timer_wait DESC
|
||
LIMIT 10;
|
||
```
|
||
|
||
---
|
||
|
||
**总结：**
|
||
- ORDER BY 是数据排序的核心工具
|
||
- 合理使用索引可以显著提升排序性能
|
||
- 多列排序时注意优先级和索引设计
|
||
- 避免在排序字段上使用函数计算
|
||
- 结合 LIMIT 使用可以优化大数据量查询
|
||
- 理解不同排名函数的差异和适用场景 |