oracle数据库中怎样删除一个表的重复数据。

Question

补充：要求只用一条sql语句完成操作。并且在只知道表结构的情况下（表内容不知）。
例如：
id name
--- ---------
1 z
1 f
2 z
1 z
3 f
1 z
表中，要删除第4、6行数据。怎么删？

Answer 1

这个问题就是要删除表中重复的记录，查询的sql语句如下：
select id,name from (
select id,name,row_numer() over(partition by id,name order by id,name) rn from tableA)
where rn>1;
删除的sql语句如下：
DELETE tableA WHERE ROWNUM = (SELECT ROWNUM
FROM (SELECT id,name,
ROW_NUMBER() OVER(PARTITION BY id,name ORDER BY id,name) RN
FROM tableA )
WHERE RN > 1);本回答被网友采纳

Answer 2

DELETE FROM TABLE TEST //删除表中重复数据
WHERE ID IN (
SELECT ID FROM TEST GROUP BY ID HAVING COUNT(*)>1//查询表中重复数据
)

Answer 3

DELETE
　　FROM table_name a
　　WHERE rowid > ( SELECT min(rowid)
　　FROM table_name b
　　WHERE b.id = a.id and b.name=a.name);本回答被提问者采纳

Answer 4

delete from table
where (id,name) in （
select id,name from table group by id,name having count(1)>1）
and rowid not in （
select min(rowid) from table group by id,name having count(1)>1）