mysql - MySQL 排序让人头疼

Question

我有一个包含名字和姓氏的表，我想检查是否没有交换数据。

桌子

+----+-----------+----------+
| Id | firstname | lastname |
+----+-----------+----------+
|  0 |  Raphael  |  Thomas  |
|  1 |   Robert  | Vincent  |
|  2 | Philippe  |  Michel  |
|  3 |  Vincent  |  Robert  |
|  4 |   Thomas  | Raphael  |
+----+-----------+----------+

我想对结果进行分组，然后突出显示任何一对

预期结果

+----+-----------+----------+
| Id | firstname | lastname |
+----+-----------+----------+
|  0 |  Raphael  |  Thomas  |
|  4 |   Thomas  | Raphael  |
|  1 |   Robert  | Vincent  |
|  3 |  Vincent  |  Robert  |
+----+-----------+----------+

这在 fisrtname+lastname 的字符数不同但总和相同时有效

SELECT t1.id, t1.lastname, t1.firstname
FROM table t1 JOIN table t2 ON t2.lastname = t1.firstname AND t2.firstname = t1.lastname
ORDER BY length(t1.lastname)+length(t1.firstname)

我也尝试转换为 HEX 并对 HEX 结果求和，但出现“DOUBLE 值超出范围”错误。

我该如何解决这个问题？

标签： mysqlsql-order-by

Answer 1

您可以在这里使用LEAST/GREATEST技巧：

SELECT
    Id,
    firstname,
    lastname
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.Id <> t1.Id AND 
              t2.lastname = t1.firstname AND t2.firstname = t1.lastname)
ORDER BY
    LEAST(firstname, lastname),
    GREATEST(firstname, lastname);

演示