MySQL:查询互斥记录集

Question

我有一个与此类似的 mysql 表:

+----+----------+----------+
| id | first    | last     +
+----+----------+-----------
|  5 | Alan     | Smith    |
|  5 | Bob      | Jones    |
|  5 | Tom      | Clark    |
|  5 | Victor   | Mars     |
|  6 | Bob      | Jones    |
|  6 | Tom      | Kelly    |
|  6 | Victor   | Mars     |
+----+----------+----------+

我想找到一个有效的查询，可以返回 id 5 和 id 6 之间不匹配的所有记录...如下所示:

+----+----------+----------+
| id | first    | last     +
+----+----------+-----------
|  5 | Alan     | Smith    |
|  5 | Tom      | Clark    |
|  6 | Tom      | Kelly    |
+----+----------+----------+

目前，我正在使用 2 个单独的查询，它们返回 2 个不同的“不在”集合，类似于以下选择。其中 5 不在 6 中，且 6 不在 5 中。

select id,
       first,
       last
  from mytable
 where id = 5  # swap 5 and 6
   and concat(first, last) 
       not in ( select concat(first, last) 
                  from mytable
                where id = 6 )   # swap 5 and 6
      group by id,
               first,
               last

有没有办法在单个查询中获取这两个集合，您能否提供使用此示例数据的示例？

有没有比我的查询更有效的方法？另外，举个例子。谢谢

Answer 1

我使用id来表示自动递增的PK。显然这里的情况并非如此，因此为了便于理解，我已重命名该列...

SELECT x.*  
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.group_id <> x.group_id 
   AND y.first = x.first 
   AND y.last = x.last 
 WHERE x.group_id IN(5,6) 
   AND y.group_id IS NULL;