跳到主要内容

SQL ORDER BY 排序

ORDER BY 子句用于对结果集进行排序。

基本语法​

SELECT 列名1, 列名2, ...
FROM 表名
ORDER BY 列名 [ASC|DESC], 列名 [ASC|DESC], ...;
  • ASC：升序（默认）
  • DESC：降序

演示表​

假设有 employees 表：

idnamedepartmentsalaryage
1张三Sales500025
2李四IT800030
3王五Sales600028
4赵六HR550035
5孙七IT750027

排序示例​

1. 单列升序（默认）​

-- 按薪资升序
SELECT * FROM employees ORDER BY salary;

-- 显式指定 ASC
SELECT * FROM employees ORDER BY salary ASC;

结果：

| 1  | 张三 | Sales   | 5000 | 25 |
| 4  | 赵六 | HR      | 5500 | 35 |
| 3  | 王五 | Sales   | 6000 | 28 |
| 5  | 孙七 | IT      | 7500 | 27 |
| 2  | 李四 | IT      | 8000 | 30 |

2. 单列降序​

-- 按薪资降序
SELECT * FROM employees ORDER BY salary DESC;

3. 多列排序​

-- 先按部门升序，再按薪资降序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

4. 按列位置排序​

-- 按第3列（salary）降序排序
SELECT name, department, salary 
FROM employees 
ORDER BY 3 DESC;

5. 按表达式排序​

-- 按薪资的百分比排序
SELECT * FROM employees 
ORDER BY salary * 1.1 DESC;

6. 按别名排序​

-- 使用别名排序
SELECT name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

排序与 NULL​

NULL 值排序位置取决于数据库：

-- MySQL: NULL 值排在最前面（升序）或最后面（降序）
SELECT * FROM employees ORDER BY age;  -- NULL 在前
SELECT * FROM employees ORDER BY age DESC;  -- NULL 在后

处理 NULL​

-- MySQL: 使用 IFNULL 将 NULL 排到最后
SELECT * FROM employees 
ORDER BY IFNULL(age, 999);

-- PostgreSQL: 使用 NULLS LAST/FIRST
SELECT * FROM employees 
ORDER BY age NULLS LAST;

排序与 LIMIT​

-- 查询薪资最高的前3名
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 3;

分页查询​

-- 第1页，每页2条
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 2 OFFSET 0;

-- 第2页，每页2条
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 2 OFFSET 2;

结合 WHERE 使用​

-- 查询 IT 部门员工，按薪资降序
SELECT * FROM employees 
WHERE department = 'IT'
ORDER BY salary DESC;

常见错误​

1. ORDER BY 位置错误​

-- 错误：ORDER BY 必须在 WHERE 之后
SELECT * FROM employees ORDER BY salary WHERE department = 'IT';

-- 正确
SELECT * FROM employees 
WHERE department = 'IT'
ORDER BY salary;

2. 列名拼写错误​

-- 错误：列名拼写错误
SELECT * FROM employees ORDER BY salarry DESC;

-- 正确
SELECT * FROM employees ORDER BY salary DESC;

3. 混合排序时忘记指定列​

-- 错误
SELECT name, salary FROM employees ORDER BY 1, 2 DESC;

-- 正确：需要为每个列指定排序方向
SELECT name, salary FROM employees ORDER BY 1 ASC, 2 DESC;

小结​

本章我们学习了：

  1. ORDER BY 基本语法
  2. ASC/DESC 升序/降序
  3. 多列排序
  4. 按别名和表达式排序
  5. LIMIT 分页

练习​

  1. 按薪资从低到高排序所有员工
  2. 按部门分组后，按薪资从高到低排序
  3. 查询薪资最高的前3名员工
  4. 实现分页查询（第2页，每页2条）