PostgreSQL 排序行 (ORDER BY)

在数据库查询中，控制结果集的排序顺序是一个基本且重要的需求。PostgreSQL 的 ORDER BY 子句提供了强大而灵活的排序功能，本文将详细介绍其语法、特性和实际应用场景。

基础概念

查询生成输出表后（在处理选择列表后），可以选择对其进行排序。如果未选择排序，则将以未指定的顺序返回行。在这种情况下，实际顺序将取决于扫描和连接计划类型以及磁盘上的顺序，但不应依赖它。

WARNING

重要提醒只有显式选择排序步骤才能保证特定的输出顺序。不要依赖数据库的默认返回顺序！

基本语法

sql

SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
         [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

语法要素说明

要素	说明	默认值
`sort_expression`	排序表达式，可以是列名、表达式或列序号	-
`ASC`	升序排列，小值在前	默认
`DESC`	降序排列，大值在前	-
`NULLS FIRST`	NULL 值排在前面	DESC 时默认
`NULLS LAST`	NULL 值排在后面	ASC 时默认

实际应用示例

示例数据准备

让我们创建一个员工表来演示各种排序场景：

sql

-- 创建员工表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(30),
    salary DECIMAL(10,2),
    hire_date DATE,
    performance_score INTEGER
);

-- 插入示例数据
INSERT INTO employees (name, department, salary, hire_date, performance_score) VALUES
('张三', '技术部', 8000.00, '2022-01-15', 85),
('李四', '销售部', 6000.00, '2021-06-10', 92),
('王五', '技术部', 9500.00, '2020-03-20', NULL),
('赵六', '人事部', 5500.00, '2023-02-28', 78),
('孙七', '销售部', 7200.00, '2021-11-05', 88),
('周八', '技术部', NULL, '2023-05-12', 90),
('吴九', '人事部', 5800.00, '2022-08-18', 82);

1. 基础单列排序

**场景：**按薪资从高到低查看员工信息

sql

-- 按薪资降序排列
SELECT name, department, salary
FROM employees
ORDER BY salary DESC;

输出结果：

    name    | department |  salary
------------+------------+----------
 王五        | 技术部     |  9500.00
 张三        | 技术部     |  8000.00
 孙七        | 销售部     |  7200.00
 李四        | 销售部     |  6000.00
 吴九        | 人事部     |  5800.00
 赵六        | 人事部     |  5500.00
 周八        | 技术部     |

分析过程：

使用 DESC 关键字实现降序排列
NULL 值（周八的薪资）默认排在最后，因为 DESC 排序时 NULLS FIRST 是默认行为，但这里显示 NULL 在最后是因为 PostgreSQL 将 NULL 视为"大于"任何非 NULL 值

2. 多列排序

**场景：**按部门分组，同部门内按薪资排序

sql

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

输出结果：

    name    | department |  salary
------------+------------+----------
 吴九        | 人事部     |  5800.00
 赵六        | 人事部     |  5500.00
 孙七        | 销售部     |  7200.00
 李四        | 销售部     |  6000.00
 王五        | 技术部     |  9500.00
 张三        | 技术部     |  8000.00
 周八        | 技术部     |

分析过程：

首先按 department 升序排列（人事部 → 销售部 → 技术部）
在同一部门内，按 salary 降序排列
每个排序条件独立生效

3. 使用表达式排序

**场景：**按员工综合评分排序（薪资 + 绩效分数）

sql

-- 按计算表达式排序
SELECT name, salary, performance_score,
       (COALESCE(salary, 0) + COALESCE(performance_score, 0) * 100) AS total_score
FROM employees
ORDER BY (COALESCE(salary, 0) + COALESCE(performance_score, 0) * 100) DESC;

输出结果：

    name    |  salary  | performance_score | total_score
------------+----------+-------------------+-------------
 王五        |  9500.00 |                   |     9500.00
 孙七        |  7200.00 |                88 |    16000.00
 李四        |  6000.00 |                92 |    15200.00
 周八        |          |                90 |     9000.00
 张三        |  8000.00 |                85 |    16500.00
 吴九        |  5800.00 |                82 |    14000.00
 赵六        |  5500.00 |                78 |    13300.00

分析过程：

使用 COALESCE 函数处理 NULL 值，避免计算结果为 NULL
排序表达式可以是任何有效的 SQL 表达式
复杂的业务逻辑可以通过表达式实现

4. 使用列别名和序号排序

**场景：**按计算字段排序，简化 ORDER BY 语句

sql

-- 使用列别名排序
SELECT name, salary,
       EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees
ORDER BY hire_year DESC, salary;

-- 使用列序号排序
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
WHERE salary IS NOT NULL
GROUP BY department
ORDER BY 2 DESC, 3 DESC;  -- 按第2列(emp_count)降序，第3列(avg_salary)降序

第一个查询输出：

    name    |  salary  | hire_year
------------+----------+-----------
 赵六        |  5500.00 |      2023
 周八        |          |      2023
 张三        |  8000.00 |      2022
 吴九        |  5800.00 |      2022
 李四        |  6000.00 |      2021
 孙七        |  7200.00 |      2021
 王五        |  9500.00 |      2020

第二个查询输出：

 department | emp_count |      avg_salary
------------+-----------+-----------------------
 技术部     |         2 | 8750.0000000000000000
 销售部     |         2 | 6600.0000000000000000
 人事部     |         2 | 5650.0000000000000000

5. NULL 值处理

**场景：**控制 NULL 值的排序位置

sql

-- NULL 值排在前面
SELECT name, salary
FROM employees
ORDER BY salary DESC NULLS FIRST;

-- NULL 值排在后面
SELECT name, salary
FROM employees
ORDER BY salary DESC NULLS LAST;

NULLS FIRST 输出：

    name    |  salary
------------+----------
 周八        |
 王五        |  9500.00
 张三        |  8000.00
 孙七        |  7200.00
 李四        |  6000.00
 吴九        |  5800.00
 赵六        |  5500.00

NULLS LAST 输出：

    name    |  salary
------------+----------
 王五        |  9500.00
 张三        |  8000.00
 孙七        |  7200.00
 李四        |  6000.00
 吴九        |  5800.00
 赵六        |  5500.00
 周八        |

高级排序技巧

1. 自定义排序顺序

**场景：**按业务优先级排序，而非字母顺序

sql

-- 按部门重要性自定义排序
SELECT name, department, salary
FROM employees
ORDER BY
    CASE department
        WHEN '技术部' THEN 1
        WHEN '销售部' THEN 2
        WHEN '人事部' THEN 3
        ELSE 4
    END,
    salary DESC;

输出结果：

    name    | department |  salary
------------+------------+----------
 王五        | 技术部     |  9500.00
 张三        | 技术部     |  8000.00
 周八        | 技术部     |
 孙七        | 销售部     |  7200.00
 李四        | 销售部     |  6000.00
 吴九        | 人事部     |  5800.00
 赵六        | 人事部     |  5500.00

2. 条件排序

**场景：**根据不同条件使用不同的排序逻辑

sql

-- 高薪员工按薪资排序，低薪员工按绩效排序
SELECT name, salary, performance_score,
    CASE
        WHEN salary > 7000 THEN '高薪'
        ELSE '一般'
    END AS salary_level
FROM employees
WHERE salary IS NOT NULL
ORDER BY
    CASE WHEN salary > 7000 THEN salary END DESC,
    CASE WHEN salary <= 7000 THEN performance_score END DESC;

排序的性能考虑

索引优化

sql

-- 为常用排序列创建索引
CREATE INDEX idx_employees_salary ON employees(salary DESC);
CREATE INDEX idx_employees_dept_salary ON employees(department, salary DESC);

-- 复合排序的复合索引
CREATE INDEX idx_employees_hire_salary ON employees(hire_date DESC, salary DESC);

排序限制

sql

-- 使用 LIMIT 减少排序开销
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;  -- 只需要前5条记录

集合操作中的排序

**场景：**对 UNION 结果进行排序

sql

-- 技术部高薪员工 + 所有销售部员工
(SELECT name, department, salary, '高薪技术' as category
 FROM employees
 WHERE department = '技术部' AND salary > 8000)
UNION ALL
(SELECT name, department, salary, '销售人员' as category
 FROM employees
 WHERE department = '销售部')
ORDER BY salary DESC NULLS LAST;

INFO

集合操作排序规则在 UNION、INTERSECT 或 EXCEPT 操作中，ORDER BY 只能使用输出列名称或列序号，不能使用表达式。

常见错误和注意事项

1. 错误的别名使用

sql

-- ❌ 错误：不能在表达式中使用别名
SELECT name, salary + 1000 AS total_salary
FROM employees
ORDER BY total_salary + 500;  -- 这会报错

-- ✅ 正确：直接使用完整表达式
SELECT name, salary + 1000 AS total_salary
FROM employees
ORDER BY salary + 1500;

2. 列名歧义

sql

-- 存在歧义的情况
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department = d.name
ORDER BY name;  -- 不清楚是哪个 name

-- 明确指定表别名
ORDER BY e.name;  -- 明确使用员工姓名排序

3. 排序选项的独立性

sql

-- ❌ 误解：以为所有列都是降序
ORDER BY department, salary DESC

-- ✅ 实际含义：department 升序，salary 降序
ORDER BY department ASC, salary DESC

-- ✅ 如果都要降序：
ORDER BY department DESC, salary DESC

排序算法原理

PostgreSQL 使用表达式数据类型的默认 B-tree 运算符类来确定 ASC 和 DESC 的排序顺序：

TIP

性能提示

对于大数据集，考虑使用索引来优化排序性能
多列排序时，索引列的顺序要与 ORDER BY 的顺序匹配
使用 LIMIT 可以显著提高 TOP-N 查询的性能

实际业务场景总结

ORDER BY 在实际业务中的典型应用：

场景	排序策略	示例
排行榜	按分数降序	`ORDER BY score DESC`
分页查询	稳定排序 + LIMIT	`ORDER BY id LIMIT 20 OFFSET 40`
报表展示	多级分组排序	`ORDER BY dept, position, salary DESC`
时间序列	按时间排序	`ORDER BY created_at DESC`
优先级处理	自定义排序	`ORDER BY CASE priority WHEN 'urgent' THEN 1...`

通过掌握这些排序技巧，您可以灵活地控制查询结果的呈现顺序，满足各种业务需求。记住，合理的排序不仅能提升用户体验，还能通过索引优化显著提高查询性能。

PostgreSQL 排序行 (ORDER BY) ​

基础概念 ​

基本语法 ​

语法要素说明 ​

实际应用示例 ​

示例数据准备 ​

1. 基础单列排序 ​

2. 多列排序 ​

3. 使用表达式排序 ​

4. 使用列别名和序号排序 ​

5. NULL 值处理 ​

高级排序技巧 ​

1. 自定义排序顺序 ​

2. 条件排序 ​

排序的性能考虑 ​

索引优化 ​

排序限制 ​

集合操作中的排序 ​

常见错误和注意事项 ​

1. 错误的别名使用 ​

2. 列名歧义 ​

3. 排序选项的独立性 ​

排序算法原理 ​

实际业务场景总结 ​

PostgreSQL 排序行 (ORDER BY)

基础概念

基本语法

语法要素说明

实际应用示例

示例数据准备

1. 基础单列排序

2. 多列排序

3. 使用表达式排序

4. 使用列别名和序号排序

5. NULL 值处理

高级排序技巧

1. 自定义排序顺序

2. 条件排序

排序的性能考虑

索引优化

排序限制

集合操作中的排序

常见错误和注意事项

1. 错误的别名使用

2. 列名歧义

3. 排序选项的独立性

排序算法原理

实际业务场景总结