互相访问过的地方的SQL查询

Question

我正在为我的大学开发一个使用 Rails 3/PostgreSQL 的项目，其中有用户、活动和场地。一个用户有很多活动，一个场地有很多活动。活动属于用户和场所，因此具有 user_id 和venue_id。

我需要的是一个 SQL 查询（或者甚至是 Rails 本身的方法？）来查找多个用户之间的共同场所。例如，我有 5 个用户访问过不同的场所。 5位用户仅参观了2个场馆。所以我想找回这2个场地。

我首先检索 5 个用户的所有活动：

SELECT a.user_id as user, a.venue_id as venue
FROM activities AS a
WHERE a.user_id=116 OR a.user_id=227 OR a.user_id=229 OR a.user_id=613 OR a.user_id=879

但现在我需要一种方法来找出共同的场所。 有什么想法吗？

谢谢， 无尾礼服

Answer 1

我并不完全熟悉 postgresql 的 sql 语法，但试试这个：


从活动中选择venue_id、COUNT(distinct user_id)
其中 user_id 在 (116,227,229,613,879)
按场地 ID 分组
有 COUNT(不同的 user_id) = 5
编辑

：

您需要将“5”更改为您关心的用户数量（您正在寻找的用户数量）。

我在这样的表结构上测试了它：

user_id     venue_id    id
----------- ----------- -----------
1           1           1
2           6           2
3           3           3
4           4           4
5           5           5
1           2           6
2           2           7
3           2           8
4           2           9
5           2           10

输出是：

venue_id    
----------- -----------
2           5

Answer 2

您必须为搜索提供一些参数。例如，5 个用户可能有 2 个共同场所，但不是 3 个。

如果您想查看这 5 个用户有哪些共同场所，您可以首先这样做：

SELECT a.venue_id, count(1) as NoOfUsers
FROM activities AS a
WHERE a.user_id=116 OR a.user_id=227 OR a.user_id=229 OR a.user_id=613 OR a.user_id=879
group by a.venue_id

这将为您带来对于这些用户有多少个用户那个场地。所以你有一定程度的“场地共享”。

但是，如果您只想查看五个用户访问过的场所，则需要在最后添加一行：

SELECT a.venue_id, count(1) as NoOfUsers
FROM activities AS a
WHERE a.user_id=116 OR a.user_id=227 OR a.user_id=229 OR a.user_id=613 OR a.user_id=879
group by a.venue_id
having count(1) = 5 --the number of users in the query

您还应该考虑将 WHERE 语句从 更改

WHERE a.user_id=116 OR a.user_id=227 OR a.user_id=229 OR a.user_id=613 OR a.user_id=879

为

WHERE a.user_id in (116, 227, 229, 613, 879)

Answer 3

在 sql 中，它会类似于：

Select distinct v.venue_id
from v.venues
join activities a on a.venue_id = v.venue_id
Join users u on u.user_id = a.user_id
Where user_id in (116,227,229,613,879)

您需要连接表，以便获取所有有过活动且有用户的场所。当您刚刚学习时，如果使用子查询，有时可视化会更简单。至少这就是我为我找到的。