如何避免SQL中ROW_NUMBER()的ORDER BY子句重复编写？

针对你遇到的ROW_NUMBER窗口函数和外层ORDER BY排序规则重复的问题，有以下几种实用解决方式：

1. 使用WINDOW子句（推荐） #

如果你的数据库支持WINDOW子句（比如PostgreSQL、MySQL 8.0+、SQL Server 2008+等），可以把排序逻辑一次性定义在窗口里，之后在窗口函数和外层排序中直接复用，修改时只需调整WINDOW子句的内容即可：

WINDOW sort_window AS (ORDER BY priority DESC NULLS LAST, created_at DESC)
SELECT
  ROW_NUMBER() OVER sort_window AS index,
  id,
  title,
  ago(created_at),
  priority,
  user_id
FROM post
ORDER BY sort_window;

2. 借助CTE/子查询预排序 #

先通过CTE或者子查询完成排序，再在外层查询中添加行号。不过要注意：部分数据库（比如PostgreSQL）中，CTE里的ORDER BY如果没有搭配LIMIT，可能会被优化器忽略，导致行号排序不符合预期，这种情况可以加一个足够大的LIMIT值：

WITH sorted_posts AS (
  SELECT
    id,
    title,
    ago(created_at) AS created_ago,
    priority,
    user_id
  FROM post
  ORDER BY priority DESC NULLS LAST, created_at DESC
  LIMIT 999999999 -- 根据数据量调整，确保覆盖所有行
)
SELECT
  ROW_NUMBER() OVER () AS index,
  *
FROM sorted_posts;

3. 不推荐：引用字段位置排序 #

这种方式通过字段在SELECT列表中的位置来指定排序，虽然能减少重复，但可读性极差，后续修改字段顺序时极易出错，仅作为备选：

SELECT
  ROW_NUMBER() OVER (ORDER BY priority DESC NULLS LAST, created_at DESC) AS index,
  id,
  title,
  ago(created_at),
  priority,
  user_id
FROM post
ORDER BY 6 DESC NULLS LAST, 4 DESC; -- 6对应priority，4对应created_at的位置

内容的提问来源于stack exchange，提问作者sudoExclamationExclamation