PostgreSQL - ORDER BY 子句

PostgreSQL 的 ORDER BY 子句对于对 SELECT 查询返回的行进行排序至关重要。您可以根据一个或多个列以升序或降序对数据进行排序，这对于创建有组织的报告、向用户展示数据以及进行某些类型的数据分析都非常关键。

语法

ORDER BY 子句的基本语法如下：

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

column1、column2 等是您要排序的列名。
ASC（升序）是默认的排序顺序，从小到大。您可以省略它。
DESC（降序）从大到小排序。
您可以按多个列排序。查询首先按 column1 排序，然后对于 column1 值相同的行，再按 column2 排序，依此类推。

设置：示例数据

为了演示 ORDER BY 子句，让我们创建一个并填充一个示例 employees 表。这将为我们的示例提供一致的数据集。

-- 创建表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE
);

-- 插入一些数据
INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('James', 'Smith', 'Sales', 55000.00, '2021-10-01'),
('Maria', 'Garcia', 'HR', 60000.00, '2020-05-15'),
('David', 'Smith', 'Engineering', 80000.00, '2022-01-20'),
('John', 'Johnson', 'Engineering', 95000.00, '2019-03-12'),
('Patricia', 'Williams', 'Sales', 58000.00, '2021-10-01'),
('Robert', 'Jones', 'Engineering', 80000.00, '2022-02-11'),
('Jennifer', 'Brown', 'HR', NULL, '2023-01-09'); -- 注意 NULL 薪水

示例 1：单列排序

让我们按薪水降序（从高到低）对所有员工进行排序。

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

此查询产生以下结果：

 first_name | last_name |  salary
------------+-----------+----------
 John       | Johnson   | 95000.00
 David      | Smith     | 80000.00
 Robert     | Jones     | 80000.00
 Maria      | Garcia    | 60000.00
 Patricia   | Williams  | 58000.00
 James      | Smith     | 55000.00
 Jennifer   | Brown     |   NULL
(7 rows)

示例 2：多列排序

现在，让我们按 department（部门）升序排序，对于同一部门的员工，再按 salary（薪水）降序排序。

SELECT department, first_name, salary
FROM employees
ORDER BY department ASC, salary DESC;

这提供了更结构化的视图，将部门分组在一起：

 department  | first_name |  salary
-------------+------------+----------
 Engineering | John       | 95000.00
 Engineering | David      | 80000.00
 Engineering | Robert     | 80000.00
 HR          | Maria      | 60000.00
 HR          | Jennifer   |   NULL
 Sales       | Patricia   | 58000.00
 Sales       | James      | 55000.00
(7 rows)

高级主题：处理 NULL 值

默认情况下，PostgreSQL 将 NULL 值视为大于任何其他值。这意味着在升序（ASC）排序中，NULL 值出现在最后；在降序（DESC）排序中，它们出现在最前面。您可以使用 NULLS FIRST 或 NULLS LAST 来覆盖此行为。

例如，要按薪水降序排序，但确保没有薪水信息的员工出现在最后：

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC NULLS LAST;

结果：

 first_name | last_name |  salary
------------+-----------+----------
 John       | Johnson   | 95000.00
 David      | Smith     | 80000.00
 Robert     | Jones     | 80000.00
 Maria      | Garcia    | 60000.00
 Patricia   | Williams  | 58000.00
 James      | Smith     | 55000.00
 Jennifer   | Brown     |   NULL
(7 rows)

性能考量

排序可能是一个开销很大的操作，尤其是在大型表上。如果您经常按特定列或一组列进行排序，在这些列上创建索引可以显著提高查询性能。数据库可以使用预排序的索引，而不是动态排序整个结果集。

-- 此索引将加速上面的多列排序示例
CREATE INDEX idx_employees_dept_salary ON employees (department ASC, salary DESC);

最佳实践

明确指定：始终指定 ASC 或 DESC 以使您的查询更清晰，即使 ASC 是默认值。
限制结果：排序大型数据集速度很慢。将 ORDER BY 与 LIMIT 结合使用可以只排序和检索前 N 个结果，这是一种非常常见且高效的模式。
索引以提高性能：对于大型表中经常排序的列，创建索引以避免代价高昂的全表排序。