ORDER BY 简介
ORDER BY 子句用于对查询结果进行排序。可以按一个或多个列进行升序（ASC）或降序（DESC）排序。如果不指定排序方向，默认为升序。
ORDER BY 的特点：
- 对查询结果进行排序
- 支持单列或多列排序
- 支持升序（ASC）和降序（DESC）
- 可以使用列名、列别名或列位置
- NULL 值的排序规则
基本语法
单列排序
-- 升序排序（默认）
SELECT * FROM table_name ORDER BY column_name;
-- 显式指定升序
SELECT * FROM table_name ORDER BY column_name ASC;
-- 降序排序
SELECT * FROM table_name ORDER BY column_name DESC;
多列排序
-- 多列排序
SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC, column3;
使用列位置排序
-- 按第2列和第3列排序
SELECT name, age, salary FROM employees
ORDER BY 2, 3 DESC;
实际示例
示例数据准备
-- 创建员工表
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
age INT
);
-- 插入测试数据
INSERT INTO employees VALUES
(1, '张三', '技术部', 8000.00, '2020-01-15', 28),
(2, '李四', '销售部', 6000.00, '2019-03-20', 32),
(3, '王五', '技术部', 9500.00, '2021-06-10', 26),
(4, '赵六', '人事部', 5500.00, '2018-11-05', 35),
(5, '钱七', '技术部', 7500.00, '2020-08-12', 29),
(6, '孙八', '销售部', 6500.00, '2019-12-01', 31);
基本排序示例
按薪资升序排序
SELECT name, department, salary
FROM employees
ORDER BY salary;
| name | department | salary |
|---|---|---|
| 赵六 | 人事部 | 5500.00 |
| 李四 | 销售部 | 6000.00 |
| 孙八 | 销售部 | 6500.00 |
| 钱七 | 技术部 | 7500.00 |
| 张三 | 技术部 | 8000.00 |
| 王五 | 技术部 | 9500.00 |
按薪资降序排序
SELECT name, department, salary
FROM employees
ORDER BY salary DESC;
按部门和薪资排序
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
| name | department | salary |
|---|---|---|
| 赵六 | 人事部 | 5500.00 |
| 孙八 | 销售部 | 6500.00 |
| 李四 | 销售部 | 6000.00 |
| 王五 | 技术部 | 9500.00 |
| 张三 | 技术部 | 8000.00 |
| 钱七 | 技术部 | 7500.00 |
高级排序技巧
使用表达式排序
-- 按年薪排序
SELECT name, salary, salary * 12 as annual_salary
FROM employees
ORDER BY salary * 12 DESC;
-- 按姓名长度排序
SELECT name, LENGTH(name) as name_length
FROM employees
ORDER BY LENGTH(name), name;
使用 CASE 语句排序
-- 自定义部门排序优先级
SELECT name, department, salary
FROM employees
ORDER BY
CASE department
WHEN '技术部' THEN 1
WHEN '销售部' THEN 2
WHEN '人事部' THEN 3
ELSE 4
END,
salary DESC;
日期排序
-- 按入职日期排序
SELECT name, hire_date,
DATEDIFF(NOW(), hire_date) as days_employed
FROM employees
ORDER BY hire_date DESC;
-- 按入职年份和月份排序
SELECT name, hire_date
FROM employees
ORDER BY YEAR(hire_date) DESC, MONTH(hire_date) DESC;
NULL 值排序
-- NULL 值排序（MySQL 中 NULL 值排在最前面）
SELECT name, bonus
FROM employees
ORDER BY bonus; -- NULL 值在前
-- 将 NULL 值排在最后
SELECT name, bonus
FROM employees
ORDER BY bonus IS NULL, bonus;
-- 将 NULL 值排在最前
SELECT name, bonus
FROM employees
ORDER BY bonus IS NOT NULL, bonus;
ORDER BY 与其他子句结合
ORDER BY + WHERE
-- 查询技术部员工，按薪资降序排列
SELECT name, salary
FROM employees
WHERE department = '技术部'
ORDER BY salary DESC;
ORDER BY + LIMIT
-- 查询薪资最高的3名员工
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
-- 查询薪资第4-6名的员工
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 3;
ORDER BY + GROUP BY
-- 按部门统计平均薪资，并按平均薪资排序
SELECT department, AVG(salary) as avg_salary, COUNT(*) as emp_count
FROM employees
GROP BY department
ORDER BY avg_salary DESC;
ORDER BY + HAVING
-- 查询平均薪资大于7000的部门，按平均薪资排序
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 7000
ORDER BY avg_salary DESC;
性能优化
排序性能优化技巧：
- 在排序列上创建索引
- 避免对大量数据进行排序
- 使用 LIMIT 限制结果集大小
- 考虑使用覆盖索引
- 避免在函数或表达式上排序
索引优化示例
-- 为排序列创建索引
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_dept_salary ON employees(department, salary);
-- 复合索引的排序优化
SELECT * FROM employees
WHERE department = '技术部'
ORDER BY salary DESC; -- 可以使用 idx_dept_salary 索引
查看排序执行计划
-- 查看查询执行计划
EXPLAIN SELECT * FROM employees ORDER BY salary DESC;
-- 查看详细执行信息
EXPLAIN FORMAT=JSON SELECT * FROM employees ORDER BY salary DESC;
实际应用场景
1. 排行榜查询
-- 销售排行榜
SELECT salesperson, SUM(amount) as total_sales
FROM sales
GROUP BY salesperson
ORDER BY total_sales DESC
LIMIT 10;
2. 分页查询
-- 分页查询员工列表（第2页，每页10条）
SELECT id, name, department, salary
FROM employees
ORDER BY id
LIMIT 10 OFFSET 10;
3. 时间序列数据
-- 按时间倒序查询最新订单
SELECT order_id, customer_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20;
4. 多维度排序
-- 学生成绩排序：先按总分，再按数学成绩，最后按姓名
SELECT student_name, math_score, english_score,
(math_score + english_score) as total_score
FROM student_scores
ORDER BY total_score DESC, math_score DESC, student_name;
常见错误和注意事项
常见错误：
- 在 GROUP BY 中使用 ORDER BY 时的列选择错误
- 忘记考虑 NULL 值的排序影响
- 在大数据集上进行无索引排序
- 混淆列位置和列名的使用
GROUP BY 与 ORDER BY 的注意事项
-- 错误：ORDER BY 中的列必须在 SELECT 中或者是聚合函数
SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY salary; -- 错误：salary 不在 SELECT 中
-- 正确：使用聚合函数
SELECT department, COUNT(*)
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;
字符串排序的字符集问题
-- 指定排序规则
SELECT name FROM employees
ORDER BY name COLLATE utf8mb4_unicode_ci;
-- 中文排序
SELECT name FROM employees
ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci;