2.delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age) 如果要留下1条,可以用 delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age and rowid>t.rowid)
delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age) 这个会把表清空吧!!! 因为子查询select * from TAB_1 where xh=t.xh and name=t.name and age=t.age永远都回返回行啊!!对于没有重复的行,这个子查询返回自身那一行嘛 delete from TAB_1 t where (select count(*) from TAB_1 where xh=t.xh and name=t.name and age=t.age)>1
不好意思,少写了条件: delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age and rowid>t.rowid) where xh=1 and name='ABC' and age=20
1、create table tb2 as select * from tb1;2、delete tb1 where where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age and rowid>t.rowid) where xh=1 and name='ABC' and age=20
1.create table tb2 as select * from tb1; 2 delete from tb1 a where rowid<(select max(rowid) from tb1 b where a.xh=b.xh and a.name=b.name and a.age=b.age; (a和 b为表的别名) 一定删得掉,如果还有其它字段相同也加上(匹配的多,精确程度就多)。
如果要留下1条,可以用
delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age and rowid>t.rowid)
这个会把表清空吧!!!
因为子查询select * from TAB_1 where xh=t.xh and name=t.name and age=t.age永远都回返回行啊!!对于没有重复的行,这个子查询返回自身那一行嘛
delete from TAB_1 t where (select count(*) from TAB_1 where xh=t.xh and name=t.name and age=t.age)>1
delete from TAB_1 t where exists (select * from TAB_1 where xh=t.xh and name=t.name and age=t.age and rowid>t.rowid) where xh=1 and name='ABC' and age=20
2 delete from tb1 a where rowid<(select max(rowid) from tb1 b where a.xh=b.xh and a.name=b.name and a.age=b.age;
(a和 b为表的别名)
一定删得掉,如果还有其它字段相同也加上(匹配的多,精确程度就多)。