oracle 基于优先级值限制结果的SQL查询

ntjbwcob 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(112)

我正在寻找一些帮助与以下任务。我正在从Map表MAP_RECRF_ASSET_TYPE中存在ASSET_TYPE的MY_TABLE中选择记录，但我得到了重复的TECHNICAL_ID。
为此，我在MAP_RECRF_ASSET_TYPE中添加了PRIORITY列，并且在结果中，我希望每个TECHNICAL_ID只有一条记录，该TECHNICAL_ID具有MAP_RECRF_ASSET_TYPE表中的ASSET_TYPE，但也具有最低的PRIORITY值（对于相同的PRIORITY值，始终只有1条记录）。
我的数据库是Oracle 19 c。
非常感谢你帮助我写这段代码。谢谢
我的当前查询：

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 *
                FROM MY_TABLE x
                WHERE
                    x.ASSET_TYPE IN (
                        SELECT ASSET_TYPE
                        FROM MAP_RECRF_ASSET_TYPE
                        WHERE APPLICATION = 'SW');

--目前的成果：

TECHNICAL_ID    ASSET_TYPE
1111    NOTIONALCR
1111    50000
2222    FWDNOTLCR
2222    50000
3333    50000

--预期成果：

TECHNICAL_ID    ASSET_TYPE
1111    NOTIONALCR
2222    FWDNOTLCR
3333    50000

oracle

来源：https://stackoverflow.com/questions/77148745/sql-query-to-limit-results-based-on-priority-value

2条答案

按热度按时间

f87krz0w1#

您可以在连接两个表的位置添加另一个CTe，它将选择其中一个优先级并添加按优先级排序的行号

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
), CTE AS (
 
                SELECT x.TECHNICAL_ID,x.ASSET_TYPE,
  ROW_NUMBER() OVER(PARTITION BY x.TECHNICAL_ID ORDER BY mrat.PRIORITY) rn
                FROM MY_TABLE x JOIN MAP_RECRF_ASSET_TYPE mrat 
  ON x.ASSET_TYPE = mrat.ASSET_TYPE
                WHERE 
                   mrat.APPLICATION = 'SW')
SELECT TECHNICAL_ID,ASSET_TYPE FROM CTE WHERE rn = 1

| 技术参数|资产类型|
| --|--|
| 1111 |概念|
| 2222 |FWDNOTLCR|
| 3333 | 50000 |
fiddle

赞(0）回复(0）举报 2023-10-16

ax6ht2ek2#

您可以使用INNER JOIN，然后使用ROW_NUMBER解析函数来查找每个technical_id的最低优先级：

with MY_TABLE (technical_id, asset_type) as (
select '1111', 'NOTIONALCR' from dual union all
select '1111', '50000'      from dual union all
select '2222', 'FWDNOTLCR'  from dual union all
select '2222', '50000'      from dual union all
select '3333', '50000'      from dual union all
select '3333', 'DUMMY'      from dual
),
MAP_RECRF_ASSET_TYPE (application, asset_type, priority) as (
select 'SW', 'NOTIONALCR', 1 from dual union all
select 'SW', 'NOTIONALDB', 1 from dual union all
select 'SW', 'FWDNOTLCR' , 2 from dual union all
select 'SW', 'FWDNOTLDR' , 2 from dual union all
select 'SW', 'SWOFFBALCR', 2 from dual union all
select 'SW', 'SWOFFBALDR', 2 from dual union all
select 'SW', 'SWFWNOTLCR', 2 from dual union all
select 'SW', 'SWFWNOTLDB', 2 from dual union all
select 'SW', '50000'     , 3 from dual
)
SELECT technical_id, asset_type
FROM   (
  SELECT t.technical_id,
         t.asset_type,
         ROW_NUMBER() OVER (
           PARTITION BY t.technical_id
           ORDER BY a.priority
         ) AS rn
  FROM   MY_TABLE t
         INNER JOIN MAP_RECRF_ASSET_TYPE a
         ON t.asset_type = a.asset_type
  WHERE  a.application = 'SW'
)
WHERE  rn = 1;

其输出：
| 技术参数|资产类型|
| --|--|
| 1111 |概念|
| 2222 |FWDNOTLCR|
| 3333 | 50000 |
fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 基于优先级值限制结果的SQL查询

2条答案

相关问题

热门标签

最新问答