mysql - 关于子查询配对结果的建议

Question

我有一个包含两列的数据表，user_id 和 preference_id。我正在努力弄清楚如何返回共享 preference_id 的 user_id 对，以及任何成对偏好的计数。

我知道这将是一个子查询问题，首先找到具有 preference_id 的 user_id，然后(在子查询中)再次检查数据库以查找具有相同 preference_id 的新 user_id(以及谁不是第一个用户)。我不知道如何构建查询。

因为我想返回两个不同的 user_id，所以我的子查询应该放在选择部分是否合乎逻辑？

--- 编辑:

表格的一个例子是:

DROP TABLE IF EXISTS `people_preferences`;
CREATE TABLE `people_preferences`
(
  `personid` int(11),
  `preferenceid` int(11)
);


INSERT INTO `people_preferences` VALUES (1,10),(1,24),(1,38),(2,1),(2,10),(2,38),(3,5),(3,38);

我相信我需要以下方面的东西:

select people_preferences.personid,
(select people_preferences.personid where....)
from people_preferences
where people_preferences.personid <> people_preferences.personid;

但显然这不会起作用，因为最终声明不会做任何明智的事情。

Answer 1

如果我没理解错的话，您需要成对的用户和计数。如果是这样:

select pp1.personid, pp2.personid,
       count(*) as num_preferences_in_common
from people_preferences pp1 join
     people_preferences pp2
     on pp1.preferenceid = pp2.preferenceid and
        pp1.personid < pp2.personid  -- only need pairs of people in one direction
group by pp1.personid, pp2.personid
order by num_preferences_in_common;