MySQL 删除连续行中的重复数据

Question

假设有下面这个表格：

ID ColA ColB
1   7    8
2   7    9
3   7    9
4   5    8
5   6    9
6   6    9
7   5    4

PK 是 ID 列。

现在，我想删除连续行中所有 ColA 和 ColB 的重复项。

在这个例子中，行 2、3 和 5、6 包含重复项。应该删除它们，只保留较大的 ID。

输出结果为：

ID ColA ColB
1   7    8

3   7    9
4   5    8

6   6    9
7   5    4

如何使用mySQL实现这个功能？

谢谢， Juergen

- Juergen

---

抱歉，朋友，但你尝试过或者至少想过什么吗？ - Aurelio De Rosa

Answer 1

SELECT 
    ID
FROM
    MyTable m1
WHERE
    0 < (SELECT 
            COUNT(*)
        FROM
            MyTable m2
        WHERE
            m2.ID = m1.ID - 1 AND 
            m2.ColA = m1.ColA AND 
            m2.ColB = m1.ColB)

然后你可以使用a

delete from MyTable where ID in ...

查询。这样，它肯定会在任何版本中都起作用。

- Lajos Arpad

---

还有一个问题未解决：我尝试了您的第一条SELECT语句，但是当我写DELETE MyTable m1 ...时，解析器会因某种原因（错误的SQL语法）而抱怨m1。 - Juergen

可能MySQL不支持此类型的删除命名，但最好使用新方法，在编辑中，因为我同时描述的问题。新方法不使用此类命名。在第一步中，您选择应删除的ID，并使用查询结果编写删除语句。问题：这是直接在MySQL中编写的，还是您从其他技术（如Java、C＃、C ++、PHP）运行命令？ - Lajos Arpad

直接在MySQL中使用您的编辑方法会出现错误1093：您不能在FROM子句中更新目标表'MyTable'；我现在将尝试使用临时表的另外两个建议。 - Juergen

@Juergen，由于修复没有解释，您可以通过在子查询中为表设置别名来避免此错误，请参见https://dev59.com/PnVD5IYBdhLWcg3wOpBh 例如：将FROM TargetTable更改为FROM（SELECT * FROM TargetTable）as TargetTable_Temp。 - quetzaluz

如果ID是按顺序排列的，那么这个解决方案就是正确的。但如果ID不是按顺序排列的，你就需要用类似于“m2.ID = <expression that returns adjacent m1.ID>”这样的表达式来替换“m2.ID = m1.ID - 1”。 - Despotovic

@Despotovic 是正确的。但问题表明了顺序性。 - Lajos Arpad

Answer 2

CREATE TEMPORARY TABLE duplicates (id int primary key)

INSERT INTO duplicates (id)
    SELECT t1.id
      FROM table t1
      join table t2 on t2.id = t1.id + 1
     WHERE t1.ColA = t2.ColA
       and t1.ColB = t2.ColB

-- SELECT * FROM duplicates --> are you happy with that? => delete
DELETE table
  FROM table
  join duplicates on table.id = duplicates.id

- DavidEG

Answer 3

根据您拥有的记录数量，这可能不是最有效的方法：

SELECT (SELECT TOP 1 id FROM table WHERE colA = m.colA AND colB = m.colB ORDER BY id DESC) AS id, m.*
FROM (SELECT DISTINCT colA, colB
      FROM  table) m

可能会有语法错误，因为我通常使用mssql，但是思路应该是类似的。

- JohnathanKong

---

MySQL 中没有 top 关键字，而是使用 Limit。此外，您不会删除重复项。此外，您的选择甚至没有选择问题中定义的重复项（连续 ID，除了序列中的第一个）。很抱歉，但我必须给您投反对票。 - Lajos Arpad

哎呀，我错过了删除部分。我读错了，是的，我应该使用LIMIT而不是TOP；那是我的MS东西在作怪。但是如果你DELETE tabe WHERE NOT IN（上面的查询），你会剩下不在嵌套选择中的ID，而嵌套选择选择第一个最高的ID，因为我按顺序排列并且我们只选择第一个。只需删除m.*即可。 - JohnathanKong

Answer 4

我已经将第一个表命名为“test”。

首先创建一个表，用于存储所有相同的ColA和ColB组合：

create temporary table tmpTable (ColA int, ColB int);
insert into tmpTable select ColA,ColB from test group by ColA, ColB;

现在，针对每个相同的ColA和ColB组合，在原始表中选择最大的id。将其放入一个新表中（称为idsToKeep，因为这些是我们不想删除的行）：

create temporary table idsToKeep (ID int);
insert into idsToKeep select (select max(ID) from test where test.ColA=tmpTable.ColA and test.ColB=tmpTable.ColB) from tmpTable;

最后，从原始表中删除不在idsToKeep表中的所有条目：

delete from test where ID <> all (select ID from idsToKeep);

- drmatt

---

这种方法保证了具有最高ID的行不会被删除。 - drmatt

如果行不是连续的，但是相同的ColA和ColB在序列中出现，那么这种方法就行不通。 - jsgoupil