ÔÚOracleÖÐÈçºÎÈ¥µôÕâÑùµÄÖظ´ÐÐ - Oracle / ¸ß¼¶¼¼Êõ

ÔÚOracleÖвéѯµÃµ½ÀàËÆÕâÑùµÄ½á¹û£º
a b ab
1 2 1,2
2 1 2,1
ÈçºÎÈ¥µôÆäÖÐÈÎÒâÒ»Ìõ¼Ç¼

---

select * from table1 t
where not exists(
  select 1 from table1 where a=t.b and b=t.a and a>t.a)

---

SQL code:
with temp as(
select 1 a,2 b,'1,2' ab from dual
union all
select 2 a,1 b,'2,1' ab from dual
)
select * from temp where a = (round(dbms_random.value)+1)

---

ÒýÓÃ

select * from table1 t
where not exists(
select 1 from table1 where a=t.b and b=t.a and a>t.a)

ÒýÓÃһϿñÀ˵ģ¬¸ÄÒ»ÏÂ:
delete from table1 t
where exists(
  select 1 from tmp where a=t.b and b=t.a and a<t.a)

---

and rownum=1 ¼Ó¸öÕâ¸ö
¾ÍÑ¡³öÒ»ÌõÁË,

---

ÒýÓÃ

ÔÚOracleÖвéѯµÃµ½ÀàËÆÕâÑùµÄ½á¹û£º
a b ab
1 2 1,2
2 1 2,1
ÈçºÎÈ¥µôÆäÖÐÈÎÒâÒ»Ìõ¼Ç¼

Â¥Ö÷µÄÃèÊö¸úÎÊÌâÑÏÖز»·ûºÏ°¡£¡

¸ù¾ÝÎÊÌâÃèÊö£¬È¥µôÈÎÒâÒ»ÐеĻ°£¬delete from tb where rownum=1; ¾ÍºÃÁË¡£

ÖÁÓÚÖظ´ÐмǼȥµôµÄ£¬Òª¿´Óм¸¸ö×ֶΣ¬²ÅºÃдsqlÓï¾ä£¬¼ÓÈëÄã±ítb ÓÐidÖ÷¼ü¡¢c1¡¢c2¡¢c3¡£ÄÇôÖظ´µÄ¼Ç¼¾ÍÊÇc1¡¢c2¡¢c3×Ö¶ÎÖظ´°É¡£
ÄÇôɾ³ýÖظ´ÐеÄsql¾ÍÊÇ
delete from tb t2
where exists(select 1 from tb t1 where t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3 and t1.id<>t2