假设我有一个像下面这样的朋友和事件表
FriendsTable {
user1_ID
user2_ID
}
Events Table {
event_ID
user_ID
}
在我的朋友表中,如果user1和user2是朋友,我只在我的数据库中存储一次user1 <user2。所以(1,2)会被找到,但(2,1)不会。>
我正试图找出一种方法,以降序返回那些不是朋友但参加相同活动的用户。例如
Events_Table{
1, 1 //user1 attended event1
1, 2 //user2 also attended event1
3, 1 //user1 attended event 2
3, 3 //user 3 attended event 3
4, 1 //user1 attended event 4
4, 3 //user3 attended event 4
3, 5 // user 5 attended event 3
}
User_table{
1, 2 //user1 is friends with user 2
}
因此,由于用户1和3不是朋友,但同时参加了事件3和4,并且用户1和5不是朋友,但参加了事件3的结果将依次为(1,3)和(1,5),因为用户1和用户3有更多相似的参加事件。
我的想法
我不想把这篇文章写得太长,但我想先用这个来找那些不是另一个朋友的朋友
select * from (select distinct f1.user1_id as user1, f2.user2_id as
user2 from FriendsTable as f1, FriendsTable as f2 where
f1.user1_id < f2.user2_id MINUS select * from FriendsTable
您可以使用基于自左连接的动态表
select * from
events where event_ID in (
select distintc e.event_ID
from events as e
left join FriendsTable as f1 on f1.user1_id = e.user_ID
left join FriendsTable as f2 on f2.user1_id = e.user_ID
where f1.user_ID is null
or f2.user_ID is null
)
order by events, user_ID;
我会说首先找到参加同一事件的所有配对,然后减去朋友:
select et1.event_ID, et1.user_id, et2.user_id
from Events_Table et1, Events_Table et2
where et1.event_ID=et2.event_ID and et1.user_ID < et2.user_id and
(et1.user_id, et2.user_id) not in (select user1_ID, user2_ID from FriendsTable)
order by et1.event_ID, et1.user_id, et2.user_id ;
select q1.user_ID1, q1.user_ID2 from
(select ET1.event_ID event_ID, ET1.user_ID user_ID1, et2.user_ID user_ID2
from EventsTable ET1 inner join EventsTable ET2 on ET1.event_ID = ET2.event_ID
where ET1.user_ID < ET2.user_ID and (select count(*) from FriendsTable where user1_ID = et1.user_id and user2_ID = et2.user_id) = 0) q1
group by q1.user_ID1, q1.user_ID2
order by count(*) desc