SQL Server 如何用 CTE + ROW_NUMBER() 模拟 DISTINCT ON

DISTINCT ON 是 postgresql 特有语法，按指定列分组并取每组排序后的首行；SQL Server 无此关键字，需用 CTE + ROW_NUMBER() 模拟：PARTITION BY 定义分组，ORDER BY 控制“首行”选择，WHERE rn = 1 提取结果。

什么是 DISTINCT ON，SQL Server 为什么没有

DISTINCT ON 是 PostgreSQL 特有的语法，用于按某列分组后取每组第一条（基于指定排序）。SQL Server 没有该关键字，但可以用 ROW_NUMBER() 配合 CTE 实现完全等价的行为：对目标分组字段去重，同时保留其他字段中按某顺序选出的“代表行”。

标准写法：CTE + ROW_NUMBER() + WHERE = 1

核心思路是：在 CTE 中用 ROW_NUMBER() 对每组编号，主查询只取序号为 1 的行。注意 PARTITION BY 必须和你希望“去重”的字段一致，ORDER BY 决定哪一行被选为“第一”。

WITH ranked AS (   select *,          ROW_NUMBER() OVER (            PARTITION BY customer_id             ORDER BY order_date DESC, order_id DESC          ) AS rn   FROM orders ) SELECT customer_id, order_id, order_date, total_amount FROM ranked WHERE rn = 1;

• PARTITION BY customer_id 表示按客户分组，每组独立编号
• ORDER BY order_date DESC, order_id DESC 确保最新订单优先被标为 rn = 1
• 不能用 TOP 1 或子查询替代 —— 它们无法跨组生效

容易踩的坑：NULL 值、排序不稳定、性能陷阱

PARTITION BY 字段含 NULL 时，所有 NULL 会被归为同一组 —— 这和 PostgreSQL 的 DISTINCT ON 行为一致，但常被忽略。若需把每个 NULL 当独立项处理，得先用 ISNULL() 或 CASE 显式转换。

• 排序字段若存在重复值（如多个订单同一天），ROW_NUMBER() 结果不确定 —— 必须加入足够区分的次级排序（例如 order_id）
• 大表上未在 PARTITION BY + ORDER BY 字段建索引，会导致排序开销剧增；建议复合索引如 (customer_id, order_date DESC, order_id DESC)
• 不要在 CTE 外再套一层 SELECT * —— 会丢失 rn 列，导致 WHERE rn = 1 报错

替代方案对比：EXISTS vs. ROW_NUMBER() vs. appLY

有人用 NOT EXISTS 子查询模拟，或用 CROSS APPLY 取 Top 1，但它们在语义和性能上都不如 CTE + ROW_NUMBER() 直观可靠：

• EXISTS 写法冗长，且难以表达“取最新一条”以外的逻辑（比如第二条、倒数第三条）
• CROSS APPLY (SELECT TOP 1 ... ORDER BY) 在多分组时可能因执行计划选择嵌套循环而变慢，尤其当驱动表无过滤条件时
• ROW_NUMBER() 是唯一能自然支持“取第 N 条”“跳过前 M 条再取”的方案，扩展性最强

真正复杂的地方不在写法本身，而在确认业务语义是否允许隐式排序依赖 —— 比如“最新订单”到底是按时间戳还是提交顺序，有没有时区或并发写入导致的时间乱序。这些必须和业务方对齐，不能只看 SQL 跑出来结果像不像。