SQL question. Find the two person having same hobbies in one table

Refresh

November 2018

Views

88 time

2

TABLE [tbl_hobby]

person_id (int) , hobby_id(int)

has many records. I want to get a SQL query to find all pairs of personid who have the same hobbies( same hobby_id ).

If A has hobby_id 1, B has too, if A doesn't have hobby_id 2, B doesn't have too, we will output A & B 's person_ids.

If A and B and C reach the limits, we output A & B , B & C, A & C.

I've finished in a very very very stupid method, multiple joins the table itself and multiple sub-queries. And of course be laughed by leader.

Is there any high performance method in a SQL for this question?

I have been thinking hard for this since 36 hrs ago......


sample data in mysql dump

CREATE TABLE `tbl_hobby` (
  `person_id` int(11) NOT NULL,
  `hobby_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tbl_hobby` (`person_id`, `hobby_id`) VALUES
(1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(2, 2),
(2, 3),(2, 4),(3, 1),(3, 2),(3, 3),(3, 4),
(4, 1),(4, 3),(4, 4),(5, 1),(5, 5),(5, 9),
(6, 2),(6, 3),(6, 4),(7, 1),(7, 3),(7, 7),
(8, 2),(8, 3),(8, 4),(9, 1),(9, 2),(9, 3),
(9, 4),(10, 1),(10, 5),(10, 9),(10, 11);
COMMIT;

Expert result: (2 and 6 and 8 same, 3 and 9 same)

2,6
2,8
6,8
3,9

Order of result records and order of the two number in one record is not important. Result record in one column or in two columns are all accepted since it can be easily concated or seperated.

2 answers

1

Альтернативная версия, без использования каких-либо имущественных обработки строк:

select distinct t1.person_id, t2.person_id
from tbl_hobby t1
join tbl_hobby t2
  on t1.person_id < t2.person_id
where 2 = all (select count(*)
               from tbl_hobby 
               where person_id in (t1.person_id, t2.person_id)
               group by hobby_id);

Может быть менее эффективным, но портативный!

4

Совокупные на человека, чтобы струны их хобби. Затем агрегат в список хобби узнать, которые принадлежат к более чем одному человеку.

select hobbies, group_concat(person_id order by person_id) as persons
from
(
  select person_id, group_concat(hobby_id order by hobby_id) as hobbies
  from tbl_hobby
  group by person_id
) persons
group by hobbies
having count(*) > 1
order by hobbies;

Это дает список аа лиц на хобби. Какой самый простой способ вывести решение, так как в противном случае мы должны были бы построить все возможные пары.

UPDATE: Если вы хотите пары, вы должны запросить таблицу дважды:

select p1.person_id as person 1, p2.person_id as person2
from
(
  select person_id, group_concat(hobby_id order by hobby_id) as hobbies
  from tbl_hobby
  group by person_id
) p1
join
(
  select person_id, group_concat(hobby_id order by hobby_id) as hobbies
  from tbl_hobby
  group by person_id
) p2 on p2.person_id > p1.person_id and p2.hobbies = p1.hobbies
order by person1, person2;