Oracle 19c：按PRIORITY筛选去重TECHNICAL_ID的SQL查询求助

针对你的需求，在Oracle 19c里可以用窗口函数ROW_NUMBER()来实现，核心思路是先关联两张表，再按TECHNICAL_ID分组，对每组内的记录按PRIORITY升序排序，只保留每组的第一条记录（即优先级最低的那条）。

修改后的完整SQL代码 #

with MY_TABLE as (
select '1111' as TECHNICAL_ID, 'NOTIONALCR' as ASSET_TYPE from dual union all
select '1111' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, 'FWDNOTLCR'  as ASSET_TYPE from dual union all
select '2222' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, '50000'      as ASSET_TYPE from dual union all
select '3333' as TECHNICAL_ID, 'DUMMY'      as ASSET_TYPE from dual
),

MAP_RECRF_ASSET_TYPE as (
select 'SW' as APPLICATION, 'NOTIONALCR' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'NOTIONALDB' as ASSET_TYPE, 1 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLCR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'FWDNOTLDR'  as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWOFFBALDR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLCR' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, 'SWFWNOTLDB' as ASSET_TYPE, 2 as PRIORITY from dual union all
select 'SW' as APPLICATION, '50000'      as ASSET_TYPE, 3 as PRIORITY from dual
)
SELECT TECHNICAL_ID, ASSET_TYPE
FROM (
    SELECT 
        x.TECHNICAL_ID, 
        x.ASSET_TYPE,
        ROW_NUMBER() OVER (PARTITION BY x.TECHNICAL_ID ORDER BY m.PRIORITY ASC) AS rn
    FROM MY_TABLE x
    JOIN MAP_RECRF_ASSET_TYPE m 
        ON x.ASSET_TYPE = m.ASSET_TYPE 
        AND m.APPLICATION = 'SW'
) t
WHERE rn = 1;

关键逻辑说明 #

1. 表关联替代IN子句：用JOIN直接关联MY_TABLE和过滤后的MAP_RECRF_ASSET_TYPE（仅保留APPLICATION='SW'的记录），避免原查询中IN子句无法直接获取优先级的问题。
2. 窗口函数分组排序：ROW_NUMBER() OVER (PARTITION BY x.TECHNICAL_ID ORDER BY m.PRIORITY ASC)会按TECHNICAL_ID分组，每组内按PRIORITY从小到大排序，给每条记录分配唯一行号。
3. 筛选唯一记录：外层查询通过rn=1筛选出每组内优先级最低的第一条记录，确保每个TECHNICAL_ID仅返回一条结果。

执行结果 #

运行上述代码后，将得到你期望的输出：

TECHNICAL_ID    ASSET_TYPE
1111            NOTIONALCR
2222            FWDNOTLCR
3333            50000

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