delete from table where id not in (select distinct id from table)
DELETE FROM tb WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM tb GROUP BY col1)tb是表名,col1是你区分重复记录的字段
DELETE MDM_PSN M WHERE NOT EXISTS (SELECT 1 FROM (SELECT MAX(ROWID) RID FROM MDM_PSN GROUP BY IDCARD) D WHERE M.ROWID=D.RID);
delete from tbl awhere rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//
delete from age where id not in (select min(id) from age group by sex);
DELETE FROM TBL A WHERE ROWID < (SELECT MAX(B.ROWID) FROM TBL B WHERE A.COL1 = B.COL1);
DELETE MDM_PSN M WHERE NOT EXISTS (SELECT 1 FROM (SELECT MAX(ROWID) RID FROM MDM_PSN GROUP BY IDCARD) D WHERE M.ROWID=D.RID);
DELETE FROM tb WHERE idNOT IN (SELECT max(ID) FROM tb GROUP BY col1)
DELETE FROM tb WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM tb GROUP BY col1)二楼的比较通用
也分享下我的 方法一: delete qqwe where (gg, hh) in (select gg, hh from qqwe group by gg, hh having count(*) > 1) and rowid not in (select min(rowid) from qqwe group by gg, hh having count(*) > 1) 或者 delete cz where rowid not in(select min(rowid) from cz group by c1,c10,c20); 说明:.适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用后者语句效率会很高
在oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中,可能所有列的内容都相同,但rowid不会相同。SQL语句如下:delete from tbl where rowid in ( select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid
在oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中,可能所有列的内容都相同,但rowid不会相同。SQL语句如下:delete from tbl where rowid in ( select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid
在oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的,rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中,可能所有列的内容都相同,但rowid不会相同。SQL语句如下:delete from tbl where rowid in ( select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid
DELETE FROM TBL A WHERE ROWID < (SELECT MAX(B.ROWID) FROM TBL B WHERE A.COL1 = B.COL1); 经测试,正确。
delete from z_cl where rowid in (select max(rowid) from z_cl t group by t.id having count(*)>1)
delete from customer a where rowid >(select min(rowid) from customer b where a.customer_xx=b.customer_xx);
delete from table a where rowid >(select min(rowid) from table b where a.col=b.col)
(SELECT 1 FROM (SELECT MAX(ROWID) RID FROM MDM_PSN GROUP BY IDCARD) D WHERE M.ROWID=D.RID);
WHERE ROWID < (SELECT MAX(B.ROWID) FROM TBL B WHERE A.COL1 = B.COL1);
(SELECT 1 FROM (SELECT MAX(ROWID) RID FROM MDM_PSN GROUP BY IDCARD) D WHERE M.ROWID=D.RID);
方法一:
delete qqwe
where (gg, hh) in
(select gg, hh from qqwe group by gg, hh having count(*) > 1)
and rowid not in
(select min(rowid) from qqwe group by gg, hh having count(*) > 1)
或者
delete cz where rowid not in(select min(rowid) from cz group by c1,c10,c20);
说明:.适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候,用后者语句效率会很高
select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid
select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid
select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2) 括号内是查询重复行的rowid