Question

oracle的自连接如何去除重复的数据呢？

select AA.fhid,BB.fhid,AA.zhid,BB.zhid
from (select a.fhid ,b.zhid,a.dy
from t_gg_fwxxfh a,t_gg_fwxxz b,t_mpb c
where b.zhid =a.zhid and c.mpid =b.mpid ) AA,
(select a.fhid ,b.zhid,a.dy
from t_gg_fwxxfh a,t_gg_fwxxz b,t_mpb c
where b.zhid =a.zhid and c.mpid =b.mpid ) BB
where AA.fhid != BB.fhid and AA.zhid=BB.zhid and AA.dy=BB.dy
结果会出现：AA.fhid BB.fhid AA.zhid BB.zhid
11 22 33 33
22 11 33 33
(会出现上边两条重复的数据)
请问应该怎样把这两条的一条给去掉？

Answer 1

select
case when AA.fhid>BB.fhid then  AA.fhid else BB.fhid end,
case when BB.fhid>AA.fhid then BB.fhid else AA.fhid end,
AA.zhid,
BB.zhid
from (select a.fhid ,b.zhid,a.dy
from t_gg_fwxxfh a,t_gg_fwxxz b,t_mpb c
where b.zhid =a.zhid and c.mpid =b.mpid ) AA,
(select a.fhid ,b.zhid,a.dy
from t_gg_fwxxfh a,t_gg_fwxxz b,t_mpb c
where b.zhid =a.zhid and c.mpid =b.mpid ) BB
where AA.fhid != BB.fhid and AA.zhid=BB.zhid and AA.dy=BB.dy
group by
case when AA.fhid>BB.fhid then  AA.fhid else BB.fhid end,
case when BB.fhid>AA.fhid then BB.fhid else AA.fhid end,
AA.zhid,
BB.zhid

仅限于当前两条数据使用，数据多了会导致前俩字段互换位置

Answer 2

select子句中使用distinct关键字

Answer 3

用 distinct

select distinct AA.fhid,BB.fhid,AA.zhid,BB.zhid...即可

Answer 4

select distinct * from tb_name a
where exists (select 1 from tb_name b where b.name=a.name)

Answer 5

后面加上一个and a.rowid>b.rowid