使用CASE排除ROW_NUMBER()中行时排序异常及修正方法

表结构与数据 #

创建表的SQL语句：

create table sales_data
(
    store int,
    sales_year int,
    sales_total float
);

表中现有数据：

有问题的查询及非预期结果 #

执行以下查询语句：

select 
    sales_year,
    sales_total,
    case 
        when sales_year <> 2022
            then row_number() over (partition by store order by sales_total) 
    end as rn
from 
    sales_data
order by 
    sales_year

得到的非预期结果：

疑问 #

为什么ROW_NUMBER()生成的序号从2开始？如何修改语句，让非2022年的行序号从1开始，同时保留所有销售年份的数据（不能用WHERE子句排除2022年的行）？

预期结果 #

原因分析 #

ROW_NUMBER()是基于整个分区内的所有行计算序号的，这里分区是store=1的全部3行，排序依据是sales_total。2022年的sales_total=60是最小的，所以它在排序中排第1位，对应序号1；但因为你用CASE语句把2022年的行的rn置为空，所以这一行的序号没显示出来，剩下的2020年行排第2位（序号2）、2021年行排第3位（序号3），看起来就像是序号从2开始。

修正方案 #

要实现只给非2022年的行生成连续的1、2序号，需要让ROW_NUMBER()只针对目标行计算序号，同时保留所有行的数据。这里提供两种兼容多数SQL数据库的方案：

方案1：在窗口函数的ORDER BY中加入条件排序 #

把2022年的行放到排序的最后，这样非2022年的行先参与序号生成，再用CASE过滤掉2022年的序号：

select 
    sales_year,
    sales_total,
    case 
        when sales_year <> 2022 then row_number() over (
            partition by store 
            order by case when sales_year <> 2022 then sales_total else null end nulls last
        ) 
    end as rn
from 
    sales_data
order by 
    sales_year;

方案2：用CTE子查询先给目标行编号，再左连接保留所有行 #

先筛选出非2022年的行并生成序号，再通过左连接把序号关联回原表，确保所有行都被保留：

with numbered_rows as (
    select 
        store,
        sales_year,
        sales_total,
        row_number() over (partition by store order by sales_total) as rn
    from sales_data
    where sales_year <> 2022
)
select 
    sd.sales_year,
    sd.sales_total,
    nr.rn
from sales_data sd
left join numbered_rows nr 
    on sd.store = nr.store 
    and sd.sales_year = nr.sales_year
order by sd.sales_year;

两种方案都能得到你想要的预期结果。

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