MySQL重复行-指定列

Question

如何运行在行之间查找重复项的查询？它需要匹配的不是一个字段，而是多个字段。

这是这张桌子的说明。

+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment | 
| token       | varchar(64)  | NO   | MUL | NULL              |                | 
| maxvar      | float        | NO   |     | NULL              |                | 
| maxvbr      | float        | NO   |     | NULL              |                | 
| minvcr      | float        | NO   |     | NULL              |                | 
| minvdr      | float        | NO   |     | NULL              |                | 
| atype       | int(11)      | NO   |     | NULL              |                | 
| avalue      | varchar(255) | NO   |     | NULL              |                | 
| createddate | timestamp    | NO   |     | CURRENT_TIMESTAMP |                | 
| timesrun    | int(11)      | NO   |     | NULL              |                | 
+-------------+--------------+------+-----+-------------------+----------------+

我需要匹配匹配的所有行: token、maxvar、maxvbr、minvcr、minvdr、type和avalue。如果所有这些字段都与另一行中的字段匹配，则将其视为“复制”。

最终，我希望将其作为delete命令运行，但我可以轻松地更改select。

UPDATE仍在寻找在MySQL中删除单个查询的解决方案

Answer 1

此查询将发现所有应删除的重复记录-

SELECT t1.id FROM table_duplicates t1
  INNER JOIN (
    SELECT MIN(id) id, token, maxvar, maxvbr, minvcr, minvdr, atype, avalue FROM table_duplicates
    GROUP BY token, maxvar, maxvbr, minvcr, minvdr, atype, avalue
    HAVING COUNT(*) > 1
  ) t2
  ON t1.id <> t2.id AND t1.token = t2.token AND t1.maxvar=t2.maxvar AND t1.maxvbr = t2.maxvbr AND t1.minvcr = t2.minvcr AND t1.minvdr = t2.minvdr AND t1.atype = t2.atype AND t1.avalue = t2.avalue;

此查询将删除所有重复项-

DELETE t1 FROM table_duplicates t1
  INNER JOIN (
    SELECT MIN(id) id, token, maxvar, maxvbr, minvcr, minvdr, atype, avalue FROM table_duplicates
    GROUP BY token, maxvar, maxvbr, minvcr, minvdr, atype, avalue
    HAVING COUNT(*) > 1
  ) t2
  ON t1.id <> t2.id AND t1.token = t2.token AND t1.maxvar=t2.maxvar AND t1.maxvbr = t2.maxvbr AND t1.minvcr = t2.minvcr AND t1.minvdr = t2.minvdr AND t1.atype = t2.atype AND t1.avalue = t2.avalue;

Answer 2

只需将表连接到自身并比较行。通过要求删除ID大于副本的id，可以确保保留id最低的副本：

DELETE FROM my_table WHERE id IN (
    SELECT DISTINCT t1.id 
    FROM my_table t1
        JOIN my_table t2 
    WHERE t1.id > t2.id
        AND t1.token = t2.token AND t1.maxvar = t2.maxvar
        AND t1.maxvbr = t2.maxvbr AND t1.minvcr = t2.minvcr
        AND t1.minvdr = t2.minvdr AND t1.type = t2.type)

Answer 3

SELECT      token,maxvar,maxvbr,minvcr,minvdr,type, avalue,
            Count(*)
FROM        yourtable
GROUP BY    token,maxvar,maxvbr,minvcr,minvdr,type, avalue
HAVING      Count(*) > 1

此查询返回表中的所有行，每次或更多次(以及它们的频率)。