SQL分区排序问题：含空值typecode的Contract数据筛选需求

需求说明 #

数据集包含contractID、typecode、typecodedate字段，typecode取值为A1、A2、B1、B2或空值，单个contractID对应多条typecode记录。筛选规则：

• 若合同存在空值typecode，直接选中该空值记录（无视typecodedate）
• 若无空值typecode，选中typecodedate最新的记录

原始数据 #

预期结果 #

原代码问题 #

尝试的SQL代码：

with a as (select 
contractID,
typecode,
typecodedate,
case when typecode = '' then row_number() over (partition by contractID order by typecode asc)
else row_number() over (partition by contractID order by typecodedate desc) end rank
from table)

select * from a where rank=1

该代码在contractID同时存在空值和非空值typecode时，会生成两条rank=1的记录，不符合筛选需求。

解决方案 #

调整排序逻辑，在分组内优先保留空值typecode记录，非空时取最新日期记录：

WITH ranked_data AS (
    SELECT 
        contractID,
        typecode,
        typecodedate,
        ROW_NUMBER() OVER (
            PARTITION BY contractID 
            ORDER BY 
                CASE WHEN typecode = '' THEN 0 ELSE 1 END ASC,
                typecodedate DESC
        ) AS rn
    FROM your_table_name
)
SELECT contractID, typecode, typecodedate
FROM ranked_data
WHERE rn = 1;

逻辑说明 #

1. 按contractID分组，排序时先用CASE将空值typecode标记为0，非空标记为1，升序排列确保空值记录排在分组最前列
2. 对于非空typecode的记录，按typecodedate降序排列，保证最新日期的记录排在前面
3. 最后筛选rn=1的记录，即可符合需求

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