17,377
社区成员
发帖
与我相关
我的任务
分享删除重复的数据，只保留一条
现有表tab (id number(8))，表中数据为一些无序的整数，且又重复，但并不是所有的数据都有重复，同时也不知那些数据是重复的，问题是：怎样才能将所有重复的数据删除，数据库中只保留一条？
...全文
请发表友善的回复…
发表回复
痞子酷 2003-06-19
- 打赏
- 举报
delete from tab
where rowid in
(
select rowid from (
select id,rowid,from tab
where id in(
select id,count(id) from tab having count(id)>1
)
group by id;
)
)
where rowid in
(
select rowid from (
select id,rowid,from tab
where id in(
select id,count(id) from tab having count(id)>1
)
group by id;
)
)
armu80830 2003-06-19
- 打赏
- 举报
DELETE FROM tab
WHERE ROWID!=(SELECT MAX(ROWID) FROM tab D WHERE tab.id=D.id);
或
delete from tab a
where rowid<(select max(rowid) from tab where id=a.id);
WHERE ROWID!=(SELECT MAX(ROWID) FROM tab D WHERE tab.id=D.id);
或
delete from tab a
where rowid<(select max(rowid) from tab where id=a.id);
痞子酷 2003-06-19
- 打赏
- 举报
1.
create table tab_bak as
select id,..., from tab
where id in (
select id,count(id) from tab having count(id)>1)
)
group by id;
2. delete from tab where id in (select id from tab_bak);
3.insert into tab as select * from tab_bak ;
create table tab_bak as
select id,..., from tab
where id in (
select id,count(id) from tab having count(id)>1)
)
group by id;
2. delete from tab where id in (select id from tab_bak);
3.insert into tab as select * from tab_bak ;
