mysql - 如何在 SQL 中比较除一列以外的所有列中都有重复的多行，并显示不同的列

Question

几天来我一直在寻找解决我的问题的方法。我尝试的任何操作都会返回不需要的结果。

基本上，我要做的是比较同一个表中的行。假设这是有问题的表:

name  |  hobby
------+------------
Bill  |  fishing     <- 1
Bill  |  basketball  <- 2
Bill  |  tennis      <- 3
Peter |  soccer
Peter |  baseball
Joe   |  tennis      <- 3
Joe   |  basketball  <- 2
Peter |  fishing
Dave  |  tennis
Joe   |  fishing     <- 1
Dave  |  fishing
Dave  |  basketball
Dave  |  football

我希望我的查询返回的是一个表格，该表格显示两个具有完全相同爱好的人的姓名，以及他们共同爱好的数量。它不得在一行中两次显示相同的名称。如果我们要对该表执行查询，结果将是:

name1   |   name2   |   nr_of_hobbies
--------+-----------+----------------
Bill    |   Joe     |   3
Joe     |   Bill    |   3

我已经尝试过各种查询，使用连接、子查询以及它们的组合，但我就是没能一针见血。我特别难以弄清楚如何比较一列中的多个项目，而其他列中的项目是相同的。我们将不胜感激任何帮助。提前致谢!

Answer 1

在 MySQL 中，最简单的方法是使用 group_concat()。注意:这会将名称放在同一列中:

select group_concat(name) as names, hobbies
from (select name, group_concat(hobby order by hobby) as hobbies
      from t
      group by name
     ) n
group by hobbies
having count(*) > 1;

如果你想要配对，你可以用 join 做同样的事情:

select n1.name as name1, n2.name as name2
from (select name, group_concat(hobby order by hobby) as hobbies
      from t
      group by name
     ) n1 join
     (select name, group_concat(hobby order by hobby) as hobbies
      from t
      group by name
     ) n2
     on n1.hobbies = n2.hobbies and n1.name <> n2.name;