PostgreSQL ORDER BY 语句

作者likuolei

PostgreSQL ORDER BY 子句完全指南

ORDER BY 是排序的灵魂，决定查询结果的顺序、可预测性、性能。
本文涵盖 语法、排序规则、多列排序、NULL 处理、索引优化、表达式排序、随机排序、分页协同、性能陷阱 等全部内容。

一、ORDER BY 基本语法

SELECT column1, column2, ...
FROM table_name
[WHERE ...]
ORDER BY 
    column1 [ASC|DESC] [NULLS FIRST|NULLS LAST],
    column2 [ASC|DESC] [NULLS FIRST|NULLS LAST],
    ...
[LIMIT ...];
选项默认值说明
ASCYes升序（小 → 大）
DESC—降序（大 → 小）
NULLS FIRST—NULL 排在前面
NULLS LASTYesNULL 排在后面

二、准备测试数据

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary NUMERIC(10,2),
    hire_date DATE,
    performance_score INT,
    is_active BOOLEAN,
    email TEXT
);

-- 插入多样化数据（含 NULL）
INSERT INTO employees (name, department, salary, hire_date, performance_score, is_active, email) VALUES
('Alice', 'IT', 85000, '2023-01-15', 95, true, 'alice@company.com'),
('Bob',   'HR', 52000, '2022-06-10', NULL, true, NULL),
('Carol', 'IT', 92000, '2024-03-20', 88, false, 'carol@company.com'),
('Dave',  'IT', NULL, '2021-11-05', 92, true, 'dave@company.com'),
('Eve',   'HR', 48000, '2023-12-01', 75, true, 'eve@company.com'),
('Frank', 'IT', 78000, NULL, 80, true, NULL),
('Grace', 'HR', 55000, '2023-05-10', 90, true, 'grace@company.com');

三、基础排序

需求SQL
按薪资降序ORDER BY salary DESC
按入职日期升序ORDER BY hire_date ASC
多列排序ORDER BY department ASC, salary DESC
-- IT 部门内薪资从高到低
SELECT name, department, salary FROM employees
WHERE department = 'IT'
ORDER BY salary DESC;

四、多列排序：优先级从左到右

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

结果：

 department | name  | salary
------------+-------+--------
 HR         | Grace |  55000
 HR         | Bob   |  52000
 HR         | Eve   |  48000
 IT         | Carol |  92000
 IT         | Alice |  85000
 IT         | Frank |  78000

五、NULL 排序行为（关键！）

排序NULL 位置
ASC最后（默认 NULLS LAST）
DESC最前（默认 NULLS LAST）
-- 薪资降序，NULL 排最后
SELECT name, salary FROM employees
ORDER BY salary DESC NULLS LAST;
-- Dave (NULL) 排最后

-- 薪资升序，NULL 排最前
SELECT name, salary FROM employees
ORDER BY salary ASC NULLS FIRST;
-- Dave (NULL) 排最前

六、表达式与函数排序

-- 按姓名长度排序
SELECT name, LENGTH(name) AS len FROM employees
ORDER BY LENGTH(name) DESC;

-- 按邮箱域名排序（@ 后部分）
SELECT name, email FROM employees
WHERE email IS NOT NULL
ORDER BY substring(email FROM '@(.+)$');

-- 按绩效得分与薪资的性价比
SELECT name, salary, performance_score,
       (performance_score::FLOAT / salary) AS ratio
FROM employees
WHERE performance_score IS NOT NULL
ORDER BY ratio DESC;

七、随机排序：ORDER BY RANDOM()

-- 随机抽取 3 名员工
SELECT name FROM employees
ORDER BY RANDOM()
LIMIT 3;

-- 每天随机推荐（可复现）
SELECT name FROM employees
ORDER BY MD5(id::TEXT || CURRENT_DATE)
LIMIT 1;

性能警告：RANDOM() 无法使用索引，全表扫描！

八、ORDER BY 与分页协同（LIMIT / OFFSET）

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

必须：ORDER BY + LIMIT 才能保证分页一致性！

九、性能优化：索引是关键

问题：无索引 → 全表排序 → 慢！

解决方案：为 ORDER BY 列建索引

-- 单列索引
CREATE INDEX idx_emp_salary ON employees(salary DESC);

-- 复合索引（多列排序）
CREATE INDEX idx_emp_dept_salary ON employees(department ASC, salary DESC);

-- 包含 NULL 排序
CREATE INDEX idx_emp_salary_nulls ON employees(salary DESC NULLS LAST);

黄金法则：索引顺序 = ORDER BY 顺序

十、表达式索引：排序复杂逻辑

-- 按姓名长度排序
CREATE INDEX idx_emp_name_len ON employees(LENGTH(name));

-- 按邮箱域名
CREATE INDEX idx_emp_email_domain ON employees(substring(email FROM '@(.+)$'));
-- 使用
SELECT name FROM employees
ORDER BY LENGTH(name) DESC;
-- 走索引！

十一、排序与 DISTINCT、GROUP BY 协同

-- 去重后排序
SELECT DISTINCT department FROM employees
ORDER BY department;

-- 分组后排序
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

十二、常见陷阱与解决方案

陷阱说明解决方案
无 ORDER BY + LIMIT结果随机必须加 ORDER BY
ORDER BY 列无索引全表排序建索引
NULLS 位置意外业务逻辑错显式 NULLS FIRST/LAST
表达式无索引慢建表达式索引
ORDER BY 与 LIMIT 不匹配分页跳页保证一致

十三、最佳实践 Checklist

项目建议
永远加 ORDER BY保证顺序
显式 ASC/DESC避免默认歧义
显式 NULLS FIRST/LAST控制 NULL 位置
建排序索引ORDER BY 列
复合索引顺序 = 排序顺序最大化命中
表达式排序 → 表达式索引性能
分页必加 ORDER BY一致性

十四、一键排序函数（可复用）

-- 通用排序：支持动态列
CREATE OR REPLACE FUNCTION sort_employees(
    p_order_by TEXT DEFAULT 'salary DESC',
    p_nulls TEXT DEFAULT 'LAST'
)
RETURNS SETOF employees AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT * FROM employees ORDER BY %s NULLS %s',
        p_order_by, p_nulls
    );
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM sort_employees('department ASC, salary DESC');

十五、快速实战：5 分钟掌握 ORDER BY

-- 1. 薪资降序，NULL 最后
SELECT name, salary FROM employees
ORDER BY salary DESC NULLS LAST;

-- 2. 部门升序，部门内入职早的在前
SELECT name, department, hire_date FROM employees
ORDER BY department ASC, hire_date ASC NULLS LAST;

-- 3. 按绩效性价比排序
SELECT name, salary, performance_score,
       (performance_score::FLOAT / NULLIF(salary, 0)) AS ratio
FROM employees
WHERE performance_score IS NOT NULL
ORDER BY ratio DESC;

-- 4. 建索引验证
CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);
EXPLAIN ANALYZE SELECT * FROM employees ORDER BY department, salary DESC;

现在就动手：
在 employees 表中：

  1. 查询 IT 部门，按 薪资降序，NULL 排最后
  2. 查询 所有员工，按 入职年份 升序（用 EXTRACT），同年按 name 排序
  3. 查询 绩效前 3 名（performance_score DESC），建索引优化
  4. 实现 随机抽取 1 名活跃员工（is_active = true）

十六、性能对比（EXPLAIN）

-- 慢：无索引
EXPLAIN ANALYZE SELECT * FROM employees ORDER BY salary DESC;

-- 快：有索引
CREATE INDEX idx_emp_salary_desc ON employees(salary DESC);
EXPLAIN ANALYZE SELECT * FROM employees ORDER BY salary DESC;

需要我生成：

  • 10万条测试数据 + 排序压力测试？回复 测试数据
  • Keyset 分页 + ORDER BY 完整实现？回复 Keyset 分页
  • 动态 ORDER BY（防注入）？回复 动态排序
  • EXPLAIN 可视化分析？回复 EXPLAIN 分析

随时告诉我！

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注