删除某些值出现多次SQL的行

2018-06-22 02:44:45

可能重复：
在Oracle中从表中删除重复的行

这是我目前拥有的一个例子：

ID        Name1    Name2
ABC123    Bob      Jones
ABC123    Bob      Smith
ABC123    Bob      Hammond
DEF234    Tim      Whatever

我想让上面的表看起来像这样：

ID        Name1    Name2
ABC123    Bob      Jones
DEF234    Tim      Whatever

我想删除共享ID字段的所有行，我不关心保留或删除哪些行，只是总行数等于唯一ID的数量。

DELETE FROM YOURTABLE Y WHERE ROWID > (SELECT min(rowid) FROM YOURTABLE X
WHERE X.ID = Y.ID)

如果您因为任何原因没有全球唯一ID，那么应该这样工作：

DELETE FROM Table WHERE CONCAT(ID,Name1,Name2) NOT IN ( 
       SELECT MIN(CONCAT(ID,Name1,Name2)) FROM Table GROUP BY ID)

 DELETE FROM your_table
      WHERE ROWID IN (
               SELECT rid
                  FROM (SELECT ROWID rid,
                           ROW_NUMBER () OVER (PARTITION BY ID ORDER BY ROWID) rn
                         FROM your_table)
                WHERE rn <> 1);

链接地址: http://www.djcxy.com/p/62065.html

上一篇: Delete rows where certain value occurs more than once SQL

下一篇: SQLite use Order By, but have another delimiter put things at bottom of list