Oracle PL/SQL中为相同CASE_ID的行分配唯一编号求助

看来你之前的思路方向有点偏，问题出在DENSE_RANK()的PARTITION BY子句上——你按CASE_ID分区，导致每个CASE_ID内部都会从1开始排名，自然会出现多个CASE_ID共享同一编号的情况。

咱们重新梳理需求，核心是给每个CASE_ID分配全局唯一的递增编号，同时满足排序规则：

• 所有属于含'SD' TRIGGER的CASE_ID的行排在最前，不受SCORE影响
• 其余行按SCORE降序排列
• 同一CASE_ID的所有行编号一致

正确的实现方式 #

我们可以先给每个CASE_ID确定全局排序优先级，再基于这个优先级生成唯一编号，最后关联回原表：

WITH case_rankings AS (
    -- 第一步：为每个CASE_ID计算全局排序的优先级，并分配唯一编号
    SELECT 
        CASE_ID,
        DENSE_RANK() OVER (
            ORDER BY 
                -- 第一优先级：含SD TRIGGER的CASE_ID排在最前面
                CASE WHEN EXISTS (
                    SELECT 1 
                    FROM TableA t 
                    WHERE t.CASE_ID = c.CASE_ID 
                      AND t.TRIGGER LIKE '%SD%'
                ) THEN 0 ELSE 1 END,
                -- 第二优先级：不含SD的CASE_ID按其最高SCORE降序排列
                MAX(SCORE) OVER (PARTITION BY c.CASE_ID) DESC,
                -- 兜底：如果SCORE相同，按CASE_ID本身排序保证编号唯一
                c.CASE_ID
        ) AS case_unique_id
    FROM TableA c
    GROUP BY c.CASE_ID
)
-- 第二步：关联原表，给每一行分配对应的CASE_ID编号，并按需求排序
SELECT 
    a.*,
    cr.case_unique_id
FROM TableA a
JOIN case_rankings cr ON a.CASE_ID = cr.CASE_ID
ORDER BY 
    -- 行级排序：含SD TRIGGER的行优先，其余按SCORE降序
    CASE WHEN a.TRIGGER LIKE '%SD%' THEN 0 ELSE 1 END,
    a.SCORE DESC;

关键逻辑说明 #

1. CASE_ID的全局排序：

   • 用EXISTS判断当前CASE_ID是否存在含'SD'的TRIGGER行，存在的话优先级设为0（排在前面），否则为1
   • 对于不含SD的CASE_ID，我们用该CASE_ID下的最大SCORE来排序，确保整个CASE_ID的排序符合需求
   • 最后加上CASE_ID作为兜底，避免因SCORE相同导致排序不稳定

2. 唯一编号生成：

   • DENSE_RANK()不需要分区，直接基于全局排序规则生成编号，这样每个CASE_ID会得到唯一的递增编号，同一CASE_ID的所有行共享这个编号

3. 行级最终排序：

   • 关联回原表后，按需求对行进行排序：含SD的行在前，其余按SCORE降序

如果你的"含SD TRIGGER"是指当前行的TRIGGER字段含SD #

如果需求是仅当前行TRIGGER含SD的那些行排在最前（而不是整个CASE_ID都排前），那我们可以调整排序逻辑：

WITH case_rankings AS (
    SELECT 
        CASE_ID,
        DENSE_RANK() OVER (
            ORDER BY 
                -- 第一优先级：存在至少一行TRIGGER含SD的CASE_ID在前
                MAX(CASE WHEN TRIGGER LIKE '%SD%' THEN 0 ELSE 1 END) OVER (PARTITION BY CASE_ID),
                -- 第二优先级：按CASE_ID的最高SCORE降序
                MAX(SCORE) OVER (PARTITION BY CASE_ID) DESC,
                CASE_ID
        ) AS case_unique_id
    FROM TableA
    GROUP BY CASE_ID
)
SELECT 
    a.*,
    cr.case_unique_id
FROM TableA a
JOIN case_rankings cr ON a.CASE_ID = cr.CASE_ID
ORDER BY 
    CASE WHEN a.TRIGGER LIKE '%SD%' THEN 0 ELSE 1 END,
    a.SCORE DESC;

为什么你的原查询不符合预期？ #

你的原查询中DENSE_RANK() OVER (partition by a.case_id order by rn)是在每个CASE_ID内部排名，所以每个CASE_ID的第一行都会得到编号1，这就导致不同的CASE_ID会重复使用编号，完全不符合"全局唯一递增编号"的要求。

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