Oracle SQL按指定生命周期类型优先级排序排名的实现咨询

我懂你的需求啦——就是在按NORM_VERSION、NORM_FULL_VERSION、NORM_PRODUCT_VALUE分组之后，让生命周期类型为internal的记录排名第1，publisher的排第2，剩下的记录再按更新时间倒序来依次递增排名对吧？

先说说你原来的查询问题：你在PARTITION BY里加了CASE WHEN LIFECYCLE_TYPE ='internal' then 1 end，这会把internal类型的记录单独划分到一个分组里，完全偏离了你想要的“同组内按类型优先级排序”的目标。

给你调整后的SQL写法，完美贴合你的需求：

SELECT *
FROM (
    SELECT 
        NORM_VERSION, 
        NORM_FULL_VERSION, 
        NORM_PRODUCT_VALUE, 
        LIFECYCLE_TYPE, 
        START_DATE,
        -- 按自定义优先级排序，同时保留更新时间倒序的规则
        ROW_NUMBER() OVER (
            PARTITION BY NORM_VERSION, NORM_FULL_VERSION, NORM_PRODUCT_VALUE
            ORDER BY 
                CASE LIFECYCLE_TYPE 
                    WHEN 'internal' THEN 1
                    WHEN 'publisher' THEN 2
                    ELSE 3  -- 其他类型优先级靠后
                END ASC,
                SYS_UPDATED_ON DESC
        ) AS dest_rank
    FROM LIFECYCLE
) ranked_data
WHERE dest_rank = 1;

关键逻辑说明： #

• 分组规则：PARTITION BY只保留你需要的三个字段，确保同版本、同产品的记录在同一组内排名。
• 优先级控制：通过CASE语句给不同的LIFECYCLE_TYPE分配优先级数值，internal设为1（ASC排序下最先），publisher设为2，其他类型设为3，这样就强制实现了你要的类型排名顺序。
• 次级排序：在同一优先级内，依然按照SYS_UPDATED_ON倒序，保证最新更新的记录排在前面。

如果你的场景允许同优先级、同更新时间的记录并列排名，可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()：

• RANK()：会跳过重复的排名（比如两个第1名后，下一个是第3名）
• DENSE_RANK()：不会跳过重复排名（两个第1名后，下一个是第2名）

根据你的实际业务需求选就行~

备注：内容来源于stack exchange，提问作者maddy