【SQL 學習】去掉重複行

“成對”的記錄 去掉其中一
其中 a----c   和 c-----a  “成對” 於是去掉其中一個，如，去掉c----a
     b----c   和 c-----b  “成對” 於是去掉其中一個，如，去掉c----b
下面是newid 提供的方法，很強大。。
least(lid,fid) 返回兩個值中的最小值
greatest(lid,fid) 返回兩個值中的最大值
SQL> create table d (lid varchar2(1) , fid varchar2(1));
Table created.
SQL> insert into d values ('a','b');
1 row created.
SQL> insert into d values ('a','c');
1 row created.
SQL> insert into d values ('a','d');
1 row created.
SQL> insert into d values ('b','c');
1 row created.
SQL> insert into d values ('b','d');
1 row created.
SQL> insert into d values ('c','a');
1 row created.
SQL> insert into d values ('c','b');
1 row created.
SQL> insert into d values ('c','f');
1 row created.
SQL> insert into d values ('d','f');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from d;
L F
- -
a b
a c
a d
b c
b d
c a
c b
c f
d f
9 rows selected.
SQL> select distinct least(lid,fid) ,greatest(lid,fid) from d;
L G
- -
c f
a d
b c
d f
a b
b d
a c
7 rows selected.
SQL> select least(lid,fid) ,greatest(lid,fid) from d;--不加distinck
L G
- -
a b
a c
a d
b c
b d
a c
b c
c f
d f
9 rows selected.