Oracle SQL:查找重复的CLIENTKEY并显示一条特定记录 [英] Oracle SQL: Find duplicate CLIENTKEY and show one specific record
问题描述
我有下表:
CLIENTKEY CLIENTNAME DEPARTMENT HOSTKEY
0201967/6 PPBOP1BOP01-JO,BLOGS KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0201967/6 PPBOP1BOP01-JO,BLOGS BS PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0024028/2 PPBOP1BOP01-FOO,BAR KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
0024028/2 PPBOP1BOP01-FOO,BAR BS PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
1746947/1 BSM1BSM03-THING,BOB BS BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0
1612105/1 WIBU1IBU03-TREE,GREEN BS WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0
我想要获得的输出:
CLIENTKEY CLIENTNAME DEPARTMENT HOSTKEY
0201967/6 PPBOP1BOP01-JO,BLOGS KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
0024028/2 PPBOP1BOP01-FOO,BAR KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
1746947/1 BSM1BSM03-THING,BOB BS BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0
1612105/1 WIBU1IBU03-TREE,GREEN BS WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0
因此,当CLIENTKEY是重复的但没有重复的CLIENTKEY时,需要显示DEPARTMENT ='KB'在哪里.
So need to show where DEPARTMENT = 'KB' when CLIENTKEY is duplicate but where there is no duplicate CLIENTKEY bring them all back.
这可以在SQL语句中实现吗?
Can this be achieved in a SQL statement?
推荐答案
您可以使用子查询为每行分配一个排名,以便在存在重复键(一个KB,一个其他键)的情况下,该KB行是排名较高;然后对此进行过滤:
You could use a subquery which assigns a ranking to each row, so that where there is a duplicate key (one KB, one anything else) the KB row is ranked higher; and then filter on that:
-- CTE for sample data
with your_table (clientkey, clientname, department, hostkey) as (
select '0201967/6', 'PPBOP1BOP01-JO,BLOGS', 'KB', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0' from dual
union all
select '0201967/6', 'PPBOP1BOP01-JO,BLOGS', 'BS', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0' from dual
union all
select '0024028/2', 'PPBOP1BOP01-FOO,BAR', 'KB', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0' from dual
union all
select '0024028/2', 'PPBOP1BOP01-FOO,BAR', 'BS', 'PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0' from dual
union all
select '1746947/1', 'BSM1BSM03-THING,BOB', 'BS', 'BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0' from dual
union all
select '1612105/1', 'WIBU1IBU03-TREE,GREEN', 'BS', 'WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0' from dual
)
-- actual query
select clientkey, clientname, department, hostkey
from (
select clientkey, clientname, department, hostkey,
rank () over (partition by clientkey
order by case when department = 'KB' then 0 else 1 end) as rnk
from your_table
)
where rnk = 1;
CLIENTKEY CLIENTNAME DE HOSTKEY
--------- --------------------- -- -----------------------------------
0024028/2 PPBOP1BOP01-FOO,BAR KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B2KI0
0201967/6 PPBOP1BOP01-JO,BLOGS KB PPBOP1BOP01/MSC/PPBOP1BOP01/2/B1KI0
1612105/1 WIBU1IBU03-TREE,GREEN BS WIBU1IBU03/SHVS/WIBU1IBU03/3/B1KI0
1746947/1 BSM1BSM03-THING,BOB BS BSM1BSM03/BSHVS/BSM1BSM03/2/B1KI0
如果可能的话,这仍将允许其他部门重复,并将包括所有这些行;它将仅排除KB的重复项.
This will still allow duplicates in other departments, if that can happen, and will include all those rows; it will only exclude duplicates for KB.
这篇关于Oracle SQL:查找重复的CLIENTKEY并显示一条特定记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!