MSSQL优雅实现按ID取by_default优先为1的最小index值

需求说明 #

现有表包含index、id、by_default、text字段，需按id分组处理：

• 优先获取该id下by_default=1的记录中index最小的行
• 若该id无by_default=1的记录，则获取by_default=0的记录中index最小的行

示例表数据 #

当前实现的问题 #

原代码需多次查询表，性能较差且写法繁琐：

declare @byd_1 as int=
(select min(t.index) idx from Table t where t.[id]=1 and t.by_default=1)
declare @byd_2 as int=
(select min(t.index) idx from Table t where t.[id]=1 and t.by_default=0)
select (case when @byd_1 is null then @byd_2 else @byd_1 end)

单行简洁解决方案（MSSQL） #

使用窗口函数ROW_NUMBER()实现一次扫描表即可完成需求，性能更优且写法简洁：

SELECT t.[index], t.id, t.by_default, t.[text]
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY id 
               ORDER BY by_default DESC, [index] ASC
           ) AS rn
    FROM [Table]
) t
WHERE t.rn = 1;

逻辑说明 #

1. PARTITION BY id：按id对记录分组
2. ORDER BY by_default DESC, [index] ASC：
   • 先按by_default降序排序，让by_default=1的记录排在同组最前面
   • 同by_default值的记录，再按index升序排序，确保取到最小的index
3. 外层查询筛选rn=1的记录，即每个id下符合优先级要求的目标行

预期结果 #

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