SQL 在sql中使用rownum删除重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21067033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete duplicate records using rownum in sql
提问by sunleo
Please help me to know delete records based on the rownum where even id is duplicate it won't happen but if it is the case.
请帮助我了解基于 rownum 的删除记录，其中即使 id 是重复的，也不会发生，但如果是这种情况。
select rownum,a.* from a;
ROWNUM ID NAME
---------- ---------- ----------
1 1 leo_1
2 2 leo_2
3 3 leo_3
4 1 leo_1
5 2 leo_2
6 3 leo_3
Query Tried but deletes all 6 rows.
查询尝试但删除所有 6 行。
DELETE FROM a
WHERE rownum not in
(SELECT MIN(rownum)
FROM a
GROUP BY name);
But this Query gives correct result:
但是这个查询给出了正确的结果：
SELECT MIN(rownum)
FROM a
GROUP BY name
ROWNUM
----------
1
2
3
Expected Result :
预期结果 ：
ROWNUM ID NAME
---------- ---------- ----------
4 1 leo_1
5 2 leo_2
6 3 leo_3
回答by Justin Cave
Use the rowid
使用 rowid
DELETE FROM table_name a
WHERE EXISTS( SELECT 1
FROM table_name b
WHERE a.id = b.id
AND a.name = b.name
AND a.rowid > b.rowid )
Of course, you could do a.rowid < b.rowidas well. The rowidis just the physical address of the row so it doesn't matter whether you delete the row that has the larger or the smaller address.
当然，你也可以这样做a.rowid < b.rowid。这rowid只是行的物理地址，因此删除具有较大或较小地址的行都没有关系。
Your expected results, though, don't make sense.
但是，您的预期结果没有意义。
Expected Result :
ROWNUM ID NAME
---------- ---------- ----------
4 1 leo_1
5 2 leo_2
6 3 leo_3
The rownumof a result set is always assigned at query time. That means that a particular row may appear with different rownumvalues in different queries (or when the same query is run multiple times). rownumis always sequential so you can never have a rownumof 4 in a result set without also having rownumvalues of 1, 2, and 3 in the same result set. Whichever duplicate row you delete, your result will be
该rownum结果集的总是在查询时分配。这意味着特定行在rownum不同的查询中可能会出现不同的值（或者在多次运行相同的查询时）。 rownum始终是连续的，因此rownum在结果集中永远不会有4 的rownum值，而同一结果集中也不会有1、2 和 3 的值。无论您删除哪个重复行，您的结果都将是
Expected Result :
预期结果 ：
ROWNUM ID NAME
---------- ---------- ----------
1 1 leo_1
2 2 leo_2
3 3 leo_3
But the rownumvalues are arbitrary. It would be just as valid for Oracle to return
但这些rownum值是任意的。Oracle 返回同样有效
Expected Result :
预期结果 ：
ROWNUM ID NAME
---------- ---------- ----------
1 2 leo_2
2 3 leo_3
3 1 leo_1
回答by Sai
DELETE FROM a
WHERE rowid not in
(SELECT MIN(rowid) FROM a group BY name);
回答by sabya
delete from tb_test where c1 in (select c1 from (select c1,c2,
row_number() over (partition by c2 order by c2) rn
from tb_test) a where a.rn >1 );
C1 is the primary key column and c2 is the column with duplicate value.
C1 是主键列，c2 是具有重复值的列。
