环境是Oracle 9i
表:table,有两列 id,name。注:表没有主键PK
现在表里的数据如下
========================
ID NAME
1 aaa
1 aaa
1 bbb
1 bbb
1 bbb
2 aaa
2 aaa
2 ppp
2 ccc
2 ccc
2 ccc
....
========================希望能删除重复的数据,但是每组重复的数据要保留一条.希望的结果如下
========================
ID NAME
1 aaa
1 bbb
2 aaa
2 ppp
2 ccc
....
========================求助这样的SQL,或者其他解决办法..多谢了!
表:table,有两列 id,name。注:表没有主键PK
现在表里的数据如下
========================
ID NAME
1 aaa
1 aaa
1 bbb
1 bbb
1 bbb
2 aaa
2 aaa
2 ppp
2 ccc
2 ccc
2 ccc
....
========================希望能删除重复的数据,但是每组重复的数据要保留一条.希望的结果如下
========================
ID NAME
1 aaa
1 bbb
2 aaa
2 ppp
2 ccc
....
========================求助这样的SQL,或者其他解决办法..多谢了!
distinct只是查询出的结果可以屏蔽掉重复的数据.
我是想要从table里删除这些多余的重复数据.一个Delete SQLthanks.
简单的举例
delete from Ytab where rowid not in (select max(rowid) from Ytab having count(*)>1 group by id,name);
delete from Ytab where rowid not in (select max(rowid) from Ytab group by id,name);
以上
delete from a a where a.rowid != (select max(rowid) from a b where a.id=b.id and a.name=b.name);
delete from a a
where a.rowid < (select max(rowid)
from a b
where a.id = b.id
and a.name = b.name);