帮助棘手的MySQL查询

Question

I'd appreciate your help on this please :

I've got a table which logs when an user (id_user) where at an event (meeting, concert..) (id_event) and his score for this event (long story). Here is my table:

CREATE TABLE `Leaderboards` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `id_event` mediumint(8) NOT NULL,
  `user_id` mediumint(9) NOT NULL,
  `score` smallint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now, I'd like to suggest to an user all the users which have been to at least one event in common, and sort it by users which have been to a lot of events in common.

I might have an idea to do it in php after a row request, but is there a smarter way to do it directly through MySQL?

Thank you for your answers!

Answer 1

I think I might have an answer for you

SELECT leader1.user_id, leader2.user_id, COUNT(leader1.id) AS num
FROM       Leaderboards AS leader1
INNER JOIN Leaderboards AS leader2 ON (
    leader1.id_event = leader2.id_event AND
    leader1.user_id != leader2.user_id
)
GROUP BY leader1.user_id, leader2.user_id
ORDER BY num DESC

This will select the ids of the users who at least one event in common, as well as the number of events. Finally it will order by the number of events in common (num) descending. If you would like to do it for a specific user, add in a where clause such as below

WHERE leader1.user_id = :user_id

I also believe this query if pretty efficient as well. Let me know if this is not quite what you need and I will see if I can make adjustments.