PostgreSQL中Row_number() over条件用法：分区优先用code缺省用new_code

该需求完全可实现，你可以使用PostgreSQL内置的COALESCE函数完成逻辑适配，该函数会按参数传入顺序返回第一个非NULL的值，刚好匹配你优先使用code、code缺失时自动取new_code的规则。

调整后的基础查询代码 #

with cte as (select *,
                        row_number() over (partition by COALESCE(code, new_code) order by price) rn_low,
                        row_number() over (partition by COALESCE(code, new_code) order by price DESC) rn_high
    from t

空字符串场景适配 #

如果你的业务定义中，code为空字符串''也属于「无有效值」的范围，COALESCE仅能识别NULL值，此时可改用CASE表达式实现逻辑：

with cte as (select *,
                        row_number() over (partition by CASE WHEN code IS NULL OR code = '' THEN new_code ELSE code END order by price) rn_low,
                        row_number() over (partition by CASE WHEN code IS NULL OR code = '' THEN new_code ELSE code END order by price DESC) rn_high
    from t

以上两种写法都不会影响后续对rn_low、rn_high字段的筛选、判断逻辑。

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