在SQL中使用rownum删除重复记录 [英] Delete duplicate records using rownum in sql

问题描述

请帮助我了解基于行号的删除记录，即使id重复，也不会发生，但是如果是这样的话.

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.

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

尝试查询，但删除所有6行.

Query Tried but deletes all 6 rows.

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

预期结果:

    ROWNUM         ID NAME
---------- ---------- ----------
         4          1 leo_1
         5          2 leo_2
         6          3 leo_3

推荐答案

使用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 )

当然，您也可以执行a.rowid < b.rowid. rowid只是该行的物理地址，因此删除具有较大或较小地址的行无关紧要.

Of course, you could do a.rowid < b.rowid as well. The rowid is 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.

不过，您的预期结果没有道理.

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

结果集的rownum始终在查询时分配.这意味着在不同的查询中(或同一查询多次运行时)特定的行可能会显示不同的rownum值. rownum始终是顺序的，因此，如果在同一结果集中没有rownum值分别为1、2和3，则结果集中的rownum永远不会为4.无论您删除哪一行重复的行，结果都是

The rownum of a result set is always assigned at query time. That means that a particular row may appear with different rownum values in different queries (or when the same query is run multiple times). rownum is always sequential so you can never have a rownum of 4 in a result set without also having rownum values of 1, 2, and 3 in the same result set. Whichever duplicate row you delete, your result will be

预期结果:

    ROWNUM         ID NAME
---------- ---------- ----------
         1          1 leo_1
         2          2 leo_2
         3          3 leo_3

但是rownum值是任意的. Oracle返回同样有效

But the rownum values are arbitrary. It would be just as valid for Oracle to return

预期结果:

    ROWNUM         ID NAME
---------- ---------- ----------
         1          2 leo_2
         2          3 leo_3
         3          1 leo_1

这篇关于在SQL中使用rownum删除重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！