在oraclesql查询中，返回唯一的行并去掉无关的行

Question

我有一个oracle查询，它通常会给我三行结果。两个是相同的，而第三个只是额外的一行。如何修改此查询以仅获得唯一的结果。以下是查询语句：

select prt.oid, it.itemname, it1.itemname, prt1.oid from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it on it.oid = prt.oid
join jrtp_rdb.xdistribports x on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269');

结果如下表所示：

00013885000000004C00426DEC534269    Flange-120491   Gate Valve-65650    0001388500000000AB3BEAC87354D9BE
00013885000000004C00426DEC534269    Flange-120491   Gate Valve-65650    0001388500000000AB3BEAC87354D9BE
00013885000000004C00426DEC534269    Flange-120491   Flange-120491       00013885000000004C00426DEC534269

在这种情况下，我只想要第一排，因为它给出了连接闸阀的法兰的名称。如何修改查询以获得唯一的行并去掉多余的第三行？

Answer 1

这个怎么样：

select distinct prt.oid, it.itemname, it1.itemname, prt1.oid from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it on it.oid = prt.oid
join jrtp_rdb.xdistribports x on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269') and prt.oid<>prt1.oid

Answer 2

查找重复项的最简单方法是添加唯一的行标识符，例如在实际物理表的情况下的ROWID：

select prt.oid, it.itemname, it1.itemname, prt1.oid 
,count(distinct  prt.rowid)  prt_cnt
,count(distinct   it.rowid)   it_cnt
,count(distinct    x.rowid)    x_cnt
,count(distinct   x1.rowid)   x1_cnt
,count(distinct   x2.rowid)   x2_cnt
,count(distinct   x3.rowid)   x3_cnt
,count(distinct  it1.rowid)  it1_cnt
,count(distinct prt1.rowid) prt1_cnt
from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it 
on it.oid = prt.oid
join jrtp_rdb.xdistribports x 
on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 
on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 
on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 
on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 
on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 
on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269')
group by prt.oid, it.itemname, it1.itemname, prt1.oid;

Answer 3

你可以试试这个

With cte as(
select prt.oid, it.itemname, it1.itemname, prt1.oid,
RANK() OVER(
PARTITION BY it.itemname,prt.oid
ORDER BY it.itemname,prt.oid DESC) r
from jrtp_rdb.jrtepartoccurrence prt
join jrtp_rdb.jnameditem it on it.oid = prt.oid
join jrtp_rdb.xdistribports x on x.oidorigin = prt.oid
join jrtp_rdb.xflowports x1 on x1.oiddestination = x.oiddestination
join jrtp_rdb.xgeneratesconnectionitems x2 on x2.oidorigin= x1.oidorigin
join jrtp_rdb.xownsimplieditems x3 on x3.oiddestination = x2.oiddestination
join jrtp_rdb.jnameditem it1 on it1.oid = x3.oidorigin
join jrtp_rdb.jrtepartoccurrence prt1 on prt1.oid = it1.oid
where prt.oid in
('00013885000000004C00426DEC534269')
)
select * from cte where r=1