delete from tab where rowid not in (select max(rowid) from tab group by 字段);这种帖子狠多啊搜索下就出来了
什么跟什么啊?怎么要用到max吗?
主要是利用rowid来删除重复记录试试不就知道了
实践证明不行! delete from tablename where colname in (select colname from tablename group by colname having count(colname)>1)却又删了所有! 我需要保留一个
delete from tab where rowid not in (select max(rowid) from tab group by 字段); delete from tablename where colname in (select colname from tablename group by colname having count(colname)>1)你自己写的那个sql肯定是全部删除 rowid不是自增列 rownum是自增列
方法一 先备份表,删除了重新插于 create table test as (select distinct * from test1) truncate table test insert into test select * from test1;drop table test1方法二 delete from test where rowid not in (select max(t1.rowid) from test1 t1 group by id,city,num)
楼主需要理解了才能改哦,^_^用rownumber() over也可以吧
delete from tab a where a.rowid not in (select max(rowid) from tab b where a.id=b.id)
delete from tablename where colname in (select colname from tablename group by colname having count(colname)>1)却又删了所有!
我需要保留一个
rowid不是自增列
rownum是自增列
先备份表,删除了重新插于
create table test as (select distinct * from test1)
truncate table test
insert into test select * from test1;drop table test1方法二
delete from test where rowid not in (select max(t1.rowid) from test1 t1 group by id,city,num)