mysql - 删除交叉引用数据

Question

我有以下 MySQL 表:

id            rid
-----       ------
1             2
2             1
2             3
3             2
1             3
3             1

我想更改此设置，以便每个关系只存在一行。

例如:

id           rid
-----       ------
1             2
2             3
1             3

Answer 1

如果你总是有对(如你的例子):

delete from table
    where id > rid;

这会保留 id 的记录更小。

如果存在不存在所有对的可能性，则:

delete t
    from table t left outer join
         (select least(id, rid) as lid, greatest(id, rid) as gid, count(*) as cnt
          from table t2
          group by least(id, rid), greatest(id, rid)
         ) t2
         on least(t.id, t.rid) = t2.lid and greatest(t.id, t.rid) = gid
    where id < rid or t2.cnt = 1;

编辑(解释):

第二个查询是如何工作的？老实说，我想写的是:

delete t from table t
   where id < rid or
         (id > rid and
          not exists (select 1 from table t2 where t2.id = t.rid and t2.rid = t.id
         );

也就是说，我想将所有记录保存在 id < rid 位置.但是，我也想保留所有单例记录 rid > id .我认为 MySQL 不允许 where 的语法条款。

相反，答案中的查询通过查看最小值和最大值来计算一对存在的次数。对于题中的数据，子查询的结果为:

id  rid  cnt
 1   2    2
 2   3    2
 1   3    2

因此，所有这些都将使用 id < rid选择行。如果你还有一行，说 4, 1 .它看起来像:

lid gid  cnt
 1   2    2
 2   3    2
 1   3    2
 1   4    1

在这种情况下，前三个将占据 id < rid 的行.但新行也将被选中，因为 cnt为 1。

如果您在表中有重复项和一个主键，那么执行相同操作的查询会有细微的变化。