寻求ROW_NUMBER()替代方案：基于参数实现SQL查询优先级切换

数据示例 #

原始数据 #

Id  Code  Inactive
1   A     0
2   A     1
3   B     0
4   C     1

期望结果 #

Id  Code  Inactive
2   A     1
3   B     0
4   C     1

核心需求 #

针对每个Code，如果存在Inactive=1的记录就返回该记录；如果没有，就返回Inactive=0的记录。

参数化查询实现 #

设有参数InactiveHasPriority，分两种场景处理：

当InactiveHasPriority = false时 #

直接查询所有Inactive=0的记录：

-- InactiveHasPriority 为 false 时的查询
select * from tbl where Inactive = 0

当InactiveHasPriority = true时（替代ROW_NUMBER子查询的高效方案） #

可以用以下两种和上述查询逻辑更贴近的写法，避免窗口函数子查询的开销：

写法一：关联查询判断优先级

-- InactiveHasPriority 为 true 时的查询（关联版）
select t.*
from tbl t
left join tbl t1 on t.Code = t1.Code and t1.Inactive = 1
where (t.Inactive = 1) or (t1.Id is null and t.Inactive = 0)

写法二：EXISTS判断存在性

-- InactiveHasPriority 为 true 时的查询（EXISTS版）
select *
from tbl t
where t.Inactive = 1
   or (t.Inactive = 0 and not exists (select 1 from tbl where Code = t.Code and Inactive = 1))

方案优势 #

这两种写法都不需要使用窗口函数，和InactiveHasPriority=false时的单条件查询结构更相似，在Code和Inactive字段有索引的情况下，执行效率更高，能快速定位符合优先级要求的记录。

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