删除除一个外的所有重复记录。

Question

我有一个表格，应该记录给定个人资料（用户 ID 到用户 ID 对）的访问者。结果我的 SQL 查询有点问题，产生了多个对而不是预期的单个对。事后看来，我应该对每个 id+id 对强制执行唯一约束。

现在，我该如何清理表格？我想做的是删除所有重复的对并只留下一个。

例如，将这个：

23515 -> 52525 date_visited
23515 -> 52525 date_visited
23515 -> 52525 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
12345 -> 54321 date_visited
23515 -> 52525 date_visited
...

转换为：

23515 -> 52525 date_visited
12345 -> 54321 date_visited

更新：如所请求，这是表结构：

id  int(10)         UNSIGNED    Non     Aucun   AUTO_INCREMENT
profile_id  int(10)         UNSIGNED    Non     0 
visitor_id  int(10)         UNSIGNED    Non     0
date_visited    timestamp           Non     CURRENT_TIMESTAMP   

- James P.

---

请问表结构是什么？是否有第三列来解决数值相等的情况？ - gbn

@gbn：表结构已添加（MySQL）。第三列用于跟踪用户最后访问个人资料的时间。该结构可能需要通过对profile_id和visitor_id设置约束进行修改。附注：我现在没有填充表的SQL，但大致上是“如果存在则更新时间戳，如果不存在则创建记录”。 - James P.

Answer 1

ANSI SQL 解决方案

在子查询中使用 group by：

delete from my_tab where id not in 
(select min(id) from my_tab group by profile_id, visitor_id);

你需要某种唯一标识符（这里我使用 id）。

MySQL解决方案

正如 @JamesPoulson 指出的那样，这会在 MySQL 中导致语法错误。正确的解决方案是（如 James 的回答 所示）：

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
);

- Frank Schmitt

---

很棒的解决方案。我没有想到使用group by (experience>knowledge)。这会显示一个“无法在FROM子句中指定目标”，但是有一个解决方法（见我的答案）。 - James P.

注意，这在MySQL中不起作用，因为它不允许您修改内部选择中使用的表： “错误代码：1093。您无法在FROM子句中指定目标表'my_tab'以进行更新。” - Desty

我已经更新了答案。我最初认为人们会阅读@JamesPoulson的评论/答案并使用他们的版本，但显然不是总是这样的。 - Frank Schmitt

如果没有ID键会怎么样？ - user3467349

非常优雅的解决方案。有时候最好使用MAX而不是MIN，这样你就可以保留最新版本的行，这些行可能是最正确的。 - Konstantin Svintsov

如果您的id字段是uuid，那么在PostgreSQL中这是行不通的，因为uuid没有最小值函数。 - undefined

Answer 2

这里是Frank Schmitt的解决方案，使用一个临时表来解决MySQL上运行的问题：

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
)

- James P.

---

@FrankSchmitt 没问题 :) - James P.

Answer 3

这样做是可行的：

With NewCTE
AS
(
Select *, Row_number() over(partition by ID order by ID)as RowNumber from 
table_name
)
Delete from NewCTE where RowNumber > 1

- Vik Wilder

---

如果您的表中没有唯一标识符并且不想创建临时表，那么这个答案是最好的。 - Manuel Hoffmann

Answer 4

选择所有唯一的行
将它们复制到一个新的临时表中
清空原始表
将临时表数据复制到原始表中

这就是我会做的事情。我不确定是否有一个查询可以为您完成所有这些操作。

- gmadd

---

使用临时表是一个好的习惯，实际上也是必要的。如果有大量数据，这可能是更适合的方法。 - James P.

Answer 5

以上答案都不适用......要求是删除所有重复项，除了每组中的一个......但是要基于多列数据。

试试这个，

 SET SERVEROUTPUT ON
 declare
 name integer := 1;
 begin    
 
    for test in (
        select tablecolumn1 , tablecolumn2, tablecolumn3, count(1) from 
        yourtable group by tablecolumn1, tablecolumn2, tablecolumn3 
        having count(1) > 1
    )
    loop
           if name <= 1000 then -- for incremental update
           
                 name := name+1;
            
                 delete from test b where 
                 b.tablecolumn1 = test.tablecolumn1  
                 and  b.tablecolumn2 = test.tablecolumn2
                 and  b.tablecolumn3 = test.tablecolumn3
                 and  rownum = 1;            
             
            end if;
    end loop;
    
    DBMS_OUTPUT.PUT_LINE(name);
     
 end;

- Manikandan Rajaminor

Answer 6

如果您正在使用SQL，您可以手动删除重复行，只需按照以下步骤：

1. 进入您的表格，其中包含重复数据。
2. 应用筛选器以将每个单独ID的重复数据分离出来
3. 选择要删除的所有行。
4. 按下删除并保存结果。
5. 为每个具有重复条目的ID重复此过程。

这是一个漫长的过程，但您可以立即在实时中看到结果。

希望这个解决方案对您有用！

- akshay choukekar

---

许多使用这些解决方案的人正在处理数百万（甚至数十亿）行数据。如果他们手动完成，需要花费数周时间。 - Marcucciboy2

什么在世界上 - courtsimas