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;