如何在MySQL中计算相似的兴趣爱好

Question

我有两张表，分别是'interests'和'users_interests'。

'users_interests'只有userid和interestid两个字段，而'interests'表则包含了id和name两个字段。

我需要找到有超过3个共同兴趣爱好ID的用户ID。有人告诉我需要使用自连接(Self Join)，但我似乎无法实现这一点。

有人说可以尝试以下方法:

SELECT 
      others.userid 
  FROM interests AS user 
  JOIN interests AS others 
      USING(interestid) 
  WHERE user.userid = 2 
  GROUP BY 
      others.userid 
  ORDER BY COUNT(*) DESC

但是我在这方面毫无进展。

- Ryan

Answer 1

SELECT ui.userid, COUNT(*) AS common_interests
FROM users_interests ui
WHERE ui.interestid IN (
    SELECT ui2.interestid FROM users_interests ui2 WHERE ui2.userid = 2
) 
AND ui.userid <> 2
GROUP BY ui.userid
HAVING common_interests > 3;

请注意代码中我们基于的userid（2）出现了两次。

- Simen Echholt

Answer 2

您说 共同拥有3个或以上的兴趣ID，那么您的意思是“至少4个”，对吗？

SELECT first1.userid, second1.userid
FROM users_interests first1, users_interests second1,
     users_interests first2, users_interests second2,
     users_interests first3, users_interests second3,
     users_interests first4, users_interests second4
WHERE
    first2.userid=first1.userid AND first3.userid=first1.userid AND first4.userid=first1.userid AND
    second2.userid=second1.userid AND second3.userid=second1.userid AND second4.userid=second1.userid AND
    first1.userid<>second1.userid AND
    first1.interestid=second1.interestid AND
    first2.interestid=second2.interestid AND first2.interestid<>first1.interestid AND
    first3.interestid=second3.interestid AND first3.interestid<>first2.interestid AND first3.interestid<>first1.interestid AND
    first4.interestid=second4.interestid AND first4.interestid<>first3.interestid AND first4.interestid<>first2.interestid AND first4.interestid<>first1.interestid

由于我没有测试过，所以请记住其中可能会有错误，只有在您理解的情况下才使用。

如果您需要相同的其他共同兴趣爱好的数量，我相信您可以编写代码动态生成此查询，如果您需要兴趣名称，则可以向interests表添加必要的四个连接并将相关列添加到SELECT子句中。

- Timwi