假设我有一张这样的桌子。
+------+------+------------+
| emp1 | emp2 | year_hired |
+------+------+------------+
| Tom | Mark | 2017 |
| Mark | Tom | 2017 |
| Tom | Eve | 2017 |
| Eve | Mark | 2017 |
| Eve | Tom | 2017 |
| Mark | Eve | 2017 |
| Alex | Jane | 2015 |
| Jane | Alex | 2015 |
+------+------+------------+
我希望删除行，这样得到的表将包含雇员的组合，而不是如下所示的排列:
+------+------+------------+
| emp1 | emp2 | year_hired |
+------+------+------------+
| Tom | Mark | 2017 |
| Tom | Eve | 2017 |
| Eve | Mark | 2017 |
| Alex | Jane | 2015 |
+------+------+------------+
如何才能做到这一点呢？ 如果可以的话，请解释一下你的答案。 我使用的是MySQL8.0
将DELETE与selfjoin一起使用。 在“Duplicates”上，保留名称按字母顺序排序的行(emp1 之后，该表将包含: 关于DB Fiddle的几点看法delete t2
from tbl t1
join tbl t2
on t2.emp1 = t1.emp2
and t2.emp2 = t1.emp1
and t2.year_hired = t1.year_hired -- optional?
where t1.emp1 < t1.emp2;
| emp1 | emp2 | year_hired |
| ---- | ---- | ---------- |
| Mark | Tom | 2017 |
| Eve | Mark | 2017 |
| Eve | Tom | 2017 |
| Alex | Jane | 2015 |
您可以在查询中使用row_number()窗口函数连接到表:
delete t
from tablename t inner join (
select *, row_number() over (partition by least(emp1, emp2), greatest(emp1, emp2), year_hired) rn
from tablename
) r on (r.emp1, r.emp2, r.year_hired) = (t.emp1, t.emp2, t.year_hired)
where r.rn > 1;
请参阅演示。
结果:
| emp1 | emp2 | year_hired |
| ---- | ---- | ---------- |
| Tom | Mark | 2017 |
| Tom | Eve | 2017 |
| Eve | Mark | 2017 |
| Alex | Jane | 2015 |
