问题

I have the following table
members:

and i want to select pairs of members only if they have the exact same hobbies (without duplicates).
So in the table above, i want the query to output:

my query:

SELECT m1.id as id1 , m2.id as id2
FROM members m1 inner join members m2
ON m1.id < m2.id
WHERE m1.hobby in (
  SELECT distinct(m2.hobby)
  )
GROUP BY id1,id2

SELECT m1.id as id1 , m2.id as id2
FROM members m1 inner join members m2
ON m1.id < m2.id
WHERE m1.hobby in (
  SELECT distinct(m2.hobby)
  )
GROUP BY id1,id2

答案1

得分: 1

以下是已翻译的内容：

一种做法是：

• 计算每个ID拥有多少个唯一的爱好
• 自连接以捕捉相同的爱好和一起的爱好数量，对不同的ID
• 确保每个ID的爱好计数等于匹配记录的计数

WITH cte AS (
    SELECT ID, 
           hobby,
           COUNT(hobby) OVER(PARTITION BY ID) AS cnt
    FROM tab 
    GROUP BY ID, 
             hobby
)
SELECT t1.ID AS id1, 
       t2.ID AS id2
FROM       cte t1
INNER JOIN cte t2
        ON t1.ID < t2.ID 
       AND t1.hobby = t2.hobby
       AND t1.cnt = t2.cnt
GROUP BY t1.ID, t2.ID, t1.cnt
HAVING COUNT(*) = t1.cnt

输出：

查看演示此处。

WITH cte AS (
    SELECT ID, 
           hobby,
           COUNT(hobby) OVER(PARTITION BY ID) AS cnt
    FROM tab 
    GROUP BY ID, 
             hobby
)
SELECT t1.ID AS id1, 
       t2.ID AS id2
FROM       cte t1
INNER JOIN cte t2
        ON t1.ID < t2.ID 
       AND t1.hobby = t2.hobby
       AND t1.cnt = t2.cnt
GROUP BY t1.ID, t2.ID, t1.cnt
HAVING COUNT(*) = t1.cnt

答案2

得分: 0

你可以通过使用 GROUP_CONCAT 将兴趣按照成员 ID 进行分组，然后使用 SUBSTRING_INDEX 来拆分连接的对：

这个查询将返回具有多个成员的兴趣：

SELECT pairs
FROM (
  SELECT hobby, GROUP_CONCAT(DISTINCT ID) as pairs
  FROM members
  GROUP BY hobby
) as s
GROUP BY pairs
HAVING COUNT(pairs) > 1

结果：

pairs
1,3

然后，逗号分隔的对将在最终查询中转换为列：

WITH cte as (
  SELECT pairs
  FROM (
    SELECT hobby, GROUP_CONCAT(DISTINCT ID) as pairs
    FROM members
    GROUP BY hobby
  ) as s
  GROUP BY pairs
  HAVING COUNT(pairs) > 1
)
SELECT SUBSTRING_INDEX(pairs, ',', 1) AS ID1,
       SUBSTRING_INDEX(pairs, ',', -1) AS ID2
FROM cte

结果：

ID1    ID2
1      3

在这里查看演示

SELECT pairs
FROM (
  select hobby, GROUP_CONCAT(DISTINCT ID) as pairs
  from members
  group by hobby
) as s
GROUP BY pairs
HAVING COUNT(pairs) > 1

pairs
1,3

WITH cte as (
  SELECT pairs
  FROM (
    select hobby, GROUP_CONCAT(DISTINCT ID) as pairs
    from members
    group by hobby
  ) as s
  GROUP BY pairs
  HAVING COUNT(pairs) > 1
)
select SUBSTRING_INDEX(pairs, ',', 1) AS ID1,
       SUBSTRING_INDEX(pairs, ',', -1) AS ID2
from cte

ID1	ID2
1	3

答案3

得分: 0

with data (id, hobby) as (
    select 1, '网球'  union all
    select 1, '足球'  union all
    select 1, '足球'  union all
    select 2, '纸牌游戏'  union all
    select 2, '绘画'  union all
    select 3, '网球'  union all
    select 3, '足球'  union all
    select 4, '纸牌游戏' union all
    select 5, '网球' union all
    select 5, '足球' union all
    select 5, '纸牌游戏'
)
, udata(id, hobby) as (
    select distinct id, hobby 
    from data
)
, cdata(id, n) as (
    select id, count(distinct hobby) as n
    from data
    group by id
)
select id1, id2 from (
    select u1.id as id1, u2.id as id2, count(*) as n, 
      c1.n as no1, c2.n as no2
    from udata u1
    join udata u2 on u2.id > u1.id and u1.hobby = u2.hobby
    join cdata c1 on c1.id = u1.id
    join cdata c2 on c2.id = u2.id
    group by u1.id, u2.id
) t
where n = no1 and n = no2
;

with data (id,hobby) as (
    select 1, 'Tennis'  union all
    select 1, 'Football'  union all
    select 1, 'Football'  union all
    select 2, 'Cards'  union all
    select 2, 'Painting'  union all
    select 3, 'Tennis'  union all
    select 3, 'Football'  union all
    select 4, 'Cards' union all
    select 5, 'Tennis' union all
    select 5, 'Football' union all
    select 5, 'Cards'
)
, udata(id,hobby) as (
    select distinct id, hobby 
    from data
)
, cdata(id, n) as (
    select id, count(distinct hobby) as n
    from data
    group by id
)
select id1, id2 from (
    select u1.id as id1, u2.id as id2, count(*) as n, 
      c1.n as no1, c2.n as no2
    from udata u1
    join udata u2 on u2.id > u1.id and u1.hobby = u2.hobby
    join cdata c1 on c1.id = u1.id
    join cdata c2 on c2.id = u2.id
    group by u1.id, u2.id
) t
where n = no1 and n = no2
;

答案4

得分: 0

以下是翻译好的部分：

"一种简单的方法是使用字符串聚合。思路是构建每个成员所有爱好的列表；然后，我们可以自连接结果以生成共享完全相同列表的用户对。

with cte as (
    select id, group_concat(distinct hobby order by hobby) hobbies
    from members
    group by id
)
select c1.id as id1, c2.id as id2, c1.hobbies
from cte c1
inner join cte c2 on c1.hobbies = c2.hobbies and c1.id < c2.id

请注意，重要的是对列表中的爱好进行排序，以便它们可以一致地进行比较。

[fiddle](https://dbfiddle.uk/fcEmKiCb)"


<details>
<summary>英文:</summary>

One simple method uses string aggregation. The idea is to build a list of all hobbies of each member; we can then self-join the result to generate pairs of users that share the exact same list.

    with cte as (
        select id, group_concat(distinct hobby order by hobby) hobbies
        from members
        group by id
    )
    select c1.id as id1, c2.id as id2, c1.hobbies
    from cte c1
    inner join cte c2 on c1.hobbies = c2.hobbies and c1.id &lt; c2.id    
    
Note that it is important  to *order* the hobbies in the lists, so they can be consistently compared.

| id1 | id2 | hobbies |
|----:|----:|:--------|
| 1 | 3 | Football,Tennis |

[fiddle](https://dbfiddle.uk/fcEmKiCb)

</details>