!!如何用一条SQL实现：如果几条记录一样，只保留一条

muchen112

!!如何用一条SQL实现：如果几条记录一样，只保留一条

laixi

select distinct * from tab

muchen112

[quote]原帖由 "laixi"]select distinct * from tab[/quote 发表：

我的意思是只保留一个，其余的都删除

holly_555

SQL>; create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /

表已建立.

SQL>; insert into a values('1111','1111');
SQL>; insert into a values('1112','1111');
SQL>; insert into a values('1113','1111');
SQL>; insert into a values('1114','1111');

SQL>; insert into a select * from a;

插入4个记录.

SQL>; commit;
SQL>; select rowid,bm,mc from a;

ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

查询到8记录.


查出重复记录
SQL>; select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111

删除重复记录
SQL>; delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

删除4个记录.

SQL>; select rowid,bm,mc from a;

ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

fish617

楼上的就是答案了!delete from table1 a  where a.rowid!=(select max(rowid) from table1 b where a.column1=b.column1 and a.column2=b.column2)

wuwei7716

想法和独特，高，实在是高 ，佩服

muchen112

thanks all

popobsd

这一招不灵, 如果这个表在复制的状态中呢?
我的错误信息是

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows


救命!!

popobsd

别告诉我要将复制状态先停下来啊.

wlkwlk

delete from tank t1 where exists (select t2.tank_no from tank t2  where t2.tank_no=t1.tank_no group by t2.tank_no
having count(*) >;1) and rowid not in (select min(rowid) from tank t3 where t3.tank_no=t1.tank_no)