PostgreSQL ORDER BY

概述

ORDER BY子句用于按一个或多个列对结果集进行升序或降序排序。它是SQL查询中最常用的子句之一。

基本语法

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

升序排序（ASC）

默认情况下，ORDER BY按升序（ASC）排序。

-- 按价格排序（升序）
SELECT * FROM products ORDER BY price;

-- 显式使用ASC关键字
SELECT * FROM products ORDER BY price ASC;

-- 按名称字母顺序排序
SELECT name, email FROM users ORDER BY name;

-- 按日期排序（最早的在前）
SELECT * FROM orders ORDER BY order_date;

降序排序（DESC）

使用DESC进行降序排序。

-- 按价格排序（降序）
SELECT * FROM products ORDER BY price DESC;

-- 按日期排序（最新的在前）
SELECT * FROM orders ORDER BY order_date DESC;

-- 按数量排序（最高的在前）
SELECT product_name, stock FROM inventory ORDER BY stock DESC;

按多列排序

可以按多列排序，每列可以有自己的排序方向。

-- 按类别（升序），然后按价格（降序）排序
SELECT * FROM products 
ORDER BY category ASC, price DESC;

-- 按国家、城市、名称排序
SELECT * FROM users 
ORDER BY country, city, name;

-- 混合排序方向
SELECT * FROM orders 
ORDER BY status ASC, order_date DESC, total DESC;

按列位置排序

可以通过SELECT列表中的位置编号引用列。

-- 按第一列排序
SELECT name, price FROM products ORDER BY 1;

-- 按第二列降序排序
SELECT name, price FROM products ORDER BY 2 DESC;

-- 按位置的多列排序
SELECT name, category, price FROM products 
ORDER BY 2, 3 DESC;

使用表达式排序

可以按计算表达式或函数排序。

-- 按计算值排序
SELECT name, price, price * 1.1 AS price_with_tax
FROM products
ORDER BY price * 1.1 DESC;

-- 按字符串长度排序
SELECT name FROM users ORDER BY LENGTH(name);

-- 按绝对值排序
SELECT value FROM numbers ORDER BY ABS(value);

-- 按日期部分排序
SELECT * FROM orders 
ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

使用CASE排序

使用CASE表达式进行自定义排序。

-- 自定义优先级顺序
SELECT * FROM tasks
ORDER BY 
    CASE priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END;

-- 带条件逻辑的排序
SELECT * FROM products
ORDER BY 
    CASE 
        WHEN category = 'Featured' THEN 1
        WHEN category = 'New' THEN 2
        ELSE 3
    END,
    price DESC;

NULL值排序

控制NULL值在排序结果中的位置。

-- NULL值在最后（ASC的默认值）
SELECT * FROM users ORDER BY phone;

-- NULL值在最前
SELECT * FROM users ORDER BY phone NULLS FIRST;

-- NULL值在最后（显式）
SELECT * FROM users ORDER BY phone NULLS LAST;

-- DESC时的NULL处理
SELECT * FROM products ORDER BY discount DESC NULLS LAST;

聚合排序

对聚合查询的结果排序。

-- 按计数排序
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

-- 按总和排序
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;

-- 按平均值排序
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

JOIN排序

对连接表的结果排序。

-- 按连接表的列排序
SELECT u.name, o.order_number, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY o.total DESC;

-- 按多个表排序
SELECT u.name, o.order_date, p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY u.name, o.order_date DESC;

子查询排序

-- 按子查询结果排序
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u
ORDER BY order_count DESC;

-- 按相关子查询排序
SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products WHERE category = p.category) AS category_avg
FROM products p
ORDER BY p.price - (SELECT AVG(price) FROM products WHERE category = p.category) DESC;

文本排序

文本排序的特殊考虑。

-- 不区分大小写排序
SELECT name FROM users ORDER BY LOWER(name);

-- 按特定排序规则排序
SELECT name FROM users ORDER BY name COLLATE "zh_CN";

-- 自然排序（数字字符串）
SELECT version FROM software ORDER BY version::text;

-- 按子字符串排序
SELECT email FROM users ORDER BY SUBSTRING(email FROM '@(.*)$');

日期和时间排序

-- 按日期排序
SELECT * FROM orders ORDER BY order_date;

-- 按时间排序
SELECT * FROM events ORDER BY event_time;

-- 按时间戳排序
SELECT * FROM logs ORDER BY created_at DESC;

-- 按日期部分排序
SELECT * FROM orders 
ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

-- 按年龄排序
SELECT name, birth_date FROM users 
ORDER BY AGE(birth_date) DESC;

DISTINCT排序

-- 对不同的值排序
SELECT DISTINCT category FROM products ORDER BY category;

-- 多列的不同值排序
SELECT DISTINCT country, city FROM users 
ORDER BY country, city;

LIMIT排序

将ORDER BY与LIMIT结合用于Top-N查询。

-- 最贵的10个产品
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 10;

-- 消费最多的5个客户
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 5;

-- 分页
SELECT * FROM products 
ORDER BY id 
LIMIT 20 OFFSET 40;  -- 第3页（第41-60行）

性能考虑

-- 为经常排序的列创建索引
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- 多个排序列的复合索引
CREATE INDEX idx_products_category_price ON products(category, price DESC);

-- 使用EXPLAIN分析排序性能
EXPLAIN ANALYZE
SELECT * FROM products ORDER BY price DESC;

-- 检查是否使用索引排序
EXPLAIN SELECT * FROM products ORDER BY price;
-- 查找"Index Scan"而不是"Sort"

常见模式

每组的前N条

-- 每个类别价格最高的3个产品
WITH ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank
    FROM products
)
SELECT * FROM ranked WHERE rank <= 3;

随机顺序

-- 随机顺序
SELECT * FROM products ORDER BY RANDOM();

-- 随机样本
SELECT * FROM products ORDER BY RANDOM() LIMIT 10;

字母数字排序

-- 字母数字字符串的自然排序
SELECT name FROM items 
ORDER BY 
    REGEXP_REPLACE(name, '[^0-9]', '', 'g')::int,
    name;

最佳实践

1. 为经常排序的列使用索引

   CREATE INDEX idx_orders_date ON orders(order_date DESC);

2. 避免对大结果集排序

   -- 好：先过滤，再排序
   SELECT * FROM orders 
   WHERE order_date >= '2024-01-01'
   ORDER BY order_date DESC;
   
   -- 避免：先排序所有，再过滤
   SELECT * FROM orders 
   ORDER BY order_date DESC
   LIMIT 1000;

3. 使用列别名提高清晰度

   SELECT 
       name,
       price * quantity AS total_value
   FROM order_items
   ORDER BY total_value DESC;

4. 显式指定排序方向

   -- 好：显式
   SELECT * FROM products ORDER BY price DESC;
   
   -- 避免：隐式（不够清晰）
   SELECT * FROM products ORDER BY price;

5. 考虑NULL处理

   SELECT * FROM users 
   ORDER BY last_login DESC NULLS LAST;

常见错误

-- ❌ 在WHERE中按别名排序（不允许）
SELECT price * 1.1 AS total FROM products 
WHERE total > 100  -- 错误！
ORDER BY total;

-- ✅ 在WHERE中使用表达式
SELECT price * 1.1 AS total FROM products 
WHERE price * 1.1 > 100
ORDER BY total;

-- ❌ 使用DISTINCT时按不在SELECT中的列排序
SELECT DISTINCT category FROM products 
ORDER BY price;  -- 错误！

-- ✅ 在SELECT中包含列
SELECT DISTINCT category, price FROM products 
ORDER BY price;

小结

ORDER BY子句要点：

• ASC：升序（默认）
• DESC：降序
• 多列：按多个条件排序
• 表达式：按计算值排序
• NULL处理：NULLS FIRST / NULLS LAST
• 性能：为排序列使用索引
• 结合使用：LIMIT、DISTINCT、GROUP BY