如何通过SQL查询筛选与指定用户有共同爱好的其他用户

数据库表结构 #

1. Users表（存储用户信息） #

示例数据：

[
  {id: 1, name: "Bob"},
  {id: 2, name: "Sam"},
  {id: 3, name: "John"}
]

2. Hobbies表（存储爱好信息） #

示例数据：

[
  {id: 1, hobby: "Soccer"},
  {id: 2, hobby: "Cooking"},
  {id: 3, hobby: "Reading"}
]

3. UsersHobby表（用户与爱好的关联表） #

示例数据：

[
  {id: 1, user_id: 1, hobby_id: 1},
  {id: 2, user_id: 1, hobby_id: 2},
  {id: 3, user_id: 2, hobby_id: 3},
  {id: 4, user_id: 3, hobby_id: 2}
]

查询需求 #

给定特定用户（比如Bob，user_id=1），要找出数据库里和他至少有一个共同爱好的其他用户：

• 现有数据下，查Bob应该返回user_id=3的John；
• 如果新增一个user_id=4、爱好是Soccer的用户，查Bob应该返回[3,4]。

目前是靠遍历该用户的爱好数组逐个查询，想找更简单高效的SQL写法。

方案一：子查询+关联 #

SELECT DISTINCT u.id, u.name
FROM Users u
JOIN UsersHobby uh ON u.id = uh.user_id
WHERE uh.hobby_id IN (
    -- 先获取目标用户的所有爱好ID
    SELECT hobby_id FROM UsersHobby WHERE user_id = 1
)
AND u.id != 1; -- 排除目标用户自身

方案二：自连接关联（大数据量场景更优） #

SELECT DISTINCT u2.id, u2.name
FROM UsersHobby uh1
-- 匹配拥有相同爱好的其他用户记录
JOIN UsersHobby uh2 ON uh1.hobby_id = uh2.hobby_id AND uh1.user_id != uh2.user_id
-- 关联用户表获取用户信息
JOIN Users u2 ON uh2.user_id = u2.id
WHERE uh1.user_id = 1;

关键说明 #

• DISTINCT用于避免同一用户因多个共同爱好被重复输出；
• 两种写法都无需循环遍历爱好数组，直接通过数据库关联逻辑完成查询，性能远高于循环查询；
• 方案二直接关联关联表匹配共同爱好，再连接用户表取信息，在数据量较大时比子查询的执行效率更高。

内容的提问来源于stack exchange，提问作者Kevin lee