Можно ли составить такой sql запрос, чтобы найти 2х пользователей с максимальным количеством общих групп(как в вк)?

Question

[ryzhak]

Здравствуйте.

Есть таблица user:
id
name

Есть таблица group(наподобие вк), в которых состоит пользователь:
id
group_name

Есть junction table, в которой хранятся пользователи и группы, в которых они состоят:
user_id
group_id

Можно ли одним красивым sql запросом вывести двух пользователей, у которых максимальное количество общих групп?

UPD:
спасибо за ответы, но гляньте пожалуйста эту демку:
sqlfiddle
должны выводиться Alice и Bob. Пользователи с максимальным количеством ОБЩИХ групп

Thanks in advance

Answer 1

[Rsa97]

SELECT `u1`.`name`, `u2`.`name`, COUNT(*) AS `count`
    FROM `users_groups` AS `ug1` 
    JOIN `users_groups` AS `ug2` 
        ON `ug2`.`group_id` = `ug1`.`group_id`
            AND `ug2`.`user_id` > `ug1`.`user_id`
    JOIN `users` AS `u1`
        ON `u1`.`id` = `ug1`.`user_id`
    JOIN `users` AS `u2`
        ON `u2`.`id` = `ug2`.`user_id`
    GROUP BY `ug1`.`user_id`, `ug2`.`user_id`
    ORDER BY `count` DESC
    LIMIT 1

Answer 2

[Shahelm]

SELECT user.id, count(*) as  numbers_of_group
    FROM user 
        JOIN user_group
            ON user_group.user_id =  user.id
GROUP BU user.id
ORDER BY numbers_of_group DESC
LIMIT 2

Красиво но не быстро)

Answer 3

[Mikhail Osher]

DEMO.

SELECT
  u.name,
  COUNT(ug.user_id) AS groups
FROM
  users u
LEFT JOIN
  users_groups ug ON
    u.id = ug.user_id
GROUP BY
  u.name
ORDER BY
  groups DESC
LIMIT 2;

Comments

[nozzy]

Логичней будет:
COUNT(ug.group_id) AS groups

Answer 4

[mletov]

SELECT user1, user2, cnt
FROM
(
    SELECT t3.name AS user1,
                 t4.name AS user2, 
                 COUNT(*) AS cnt
    FROM users_groups AS t1
    INNER JOIN users_groups AS t2
    ON t1.user_id<t2.user_id 
    AND t1.group_id=t2.group_id
    LEFT JOIN users AS t3
    ON t1.user_id=t3.id
    LEFT JOIN users AS t4
    ON t2.user_id=t4.id
    GROUP BY t1.user_id, t2.user_id, t3.name, t4.name
) AS t1
ORDER BY cnt DESC
LIMIT 0,1

Кстати, задача поставлена не совсем полно, ведь таких пар может быть несколько