sql查询以收集具有共同项目的用户

Question

我遇到了 Postgres 的问题。这是示例:我有 3 个表:用户、项目和盒子

盒子表:

user_id    |   item_id
   1       |      3
   1       |      4
   1       |      6
   1       |      7
   2       |      5
   2       |      10
   2       |      11
   3       |      5
   3       |      6
   3       |      7

鉴于此 boxes 表，我想在共享最少 2 个的用户中检索项目。因此预期的 SQL 查询结果应该是item_id: 6, 7因为用户 1 和用户 3 共享项目 6 和 7。但是用户 2 和 3 只共享一个项目:项目 5，所以项目 5 不在结果中。

我尝试了很多方法都没有成功。我想知道是否有人可以帮助我。

Answer 1

试试这个。它返回 6 和 7(如果您添加记录“1,5”，则返回 5,6,7)，但我尚未对其进行广泛测试。

-- The Outer query gets all the item_ids matching the user_ids returned from the subquery    
SELECT DISTINCT c.item_id FROM boxes c -- need DISTINCT because we get 1,3 and 3,1...
    INNER JOIN boxes d ON c.item_id = d.item_id
    INNER JOIN
        --- the subquery gets all the combinations of user ids which have more than one shared item_id
        (SELECT a.user_id as first_user,b.user_id as second_user FROM
        boxes a
        INNER JOIN boxes b ON a.item_id = b.item_id AND a.user_id <> b.user_id -- don't count items where the user_id is the same! Could just make the having clause be > 2 but this way is clearer
        GROUP BY a.user_id,b.user_id
        HAVING count(*) > 1) s
    ON s.first_user = c.user_id AND s.second_user = d.user_id