The question you're asking appears subjective and is likely to be closed.
当我填写标题字段时,当我看到上面的可怕警告时,我并不感到惊讶。
我读过几乎每个关于friends of friends
或mutual friends
的帖子,但我不确定我是否找到了我想做的正确的解决方案。
对不起,我英语不好,SQL也不好。
我怎么才能找到正确的答案,而不是两种语言都好呢?
我决定我得问一问。我不会为down-vote
s或任何duplication warning
s而失望。
因为我想要答案,所以我会尽可能真诚地写下来,因为任何进一步的类似问题都可以得到帮助。
我有一张供朋友关系用的桌子。
FRIEND (TABLE)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
1 3 // 1 knows 3
2 3 // 2 knows 3
2 4 // 2 knows 4
2 5 // 2 knows 5 // updated
3 5 // 3 knows 5 // updated
1 100
1 200
1 300
100 400
200 400
300 400
这两个composite primary keys
也都是来自PLAYER
表的外键。
我问我,得到了这么好的人的回答:“人们相互认识”。
SQL view for acquaintance from table。
我有一个这样的视野。
ACQUAINTANCE (VIEW)
-----------------------------------
PLAYER_ID(PK,FK) FRIEND_ID(PK,FK)
-----------------------------------
1 2 // 1 knows 2
2 1 // 2 knows 1
正如您可能注意到的,此关系的业务逻辑有以下两个目的。
当一个玩家说他或她认识另一个人时,他们可以说acquaintance.。
现在,我想知道有没有什么好办法
选择其他给定的玩家( PLAYER_IDs
我认为贾斯汀·尼斯纳在"people you may know" sql query中的回答是我必须遵循的最接近的道路。
提前谢谢。
如果这个主题真的是重复的并且没有必要,我会关闭这个主题。
更新
对于Raphaël Althaus的评论whose name is same with my future daughter
(这是男孩的名字吗?)
3是friends of friends of 1
的候选者,因为
1 knows 2
2 knows 3
但被排除在外,因为
1 already knows 3
基本上我想为given player
服务
people he or she may know
which is not himself or herself // this is nothing but obvious
which each is not already known to himself
使用上面的表格
by 1 -> 2 -> 4 and 1 -> 3 -> 5
4 and 5 can be suggested for 1 as 'people you may know'
order by number of mutual friends will be perfect
but I don't think I can understand even if someone show me how. sorry.
谢谢。
更新
我认为我必须一步一步地尝试,从我所学到的FROM HERE WITH VARIOUS PEOPLE
,即使这不是正确的答案。如果我做错了什么,请告诉我。
首先,让我自己加入FRIEND表本身。
SELECT *
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
打印
+-----------+-----------+-----------+-----------+
| PLAYER_ID | FRIEND_ID | PLAYER_ID | FRIEND_ID |
+-----------+-----------+-----------+-----------+
| 1 | 2 | 2 | 1 |
| 1 | 2 | 2 | 3 |
| 1 | 2 | 2 | 4 |
| 1 | 2 | 2 | 5 |
| 1 | 3 | 3 | 5 |
| 2 | 1 | 1 | 2 |
| 2 | 1 | 1 | 3 |
| 2 | 3 | 3 | 5 |
+-----------+-----------+-----------+-----------+
仅限F2.FRIEND_ID
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
打印
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
| 2 |
| 3 |
| 5 |
+-----------+
仅适用于1
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1;
打印
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
不是1
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1;
打印
+-----------+
| FRIEND_ID |
+-----------+
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
不是1的直接已知
SELECT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
打印
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
+-----------+
我想我快到了。
更新
添加了以下路径
1 -> 100 -> 400
1 -> 200 -> 400
1 -> 300 -> 400
最后一个查询(再次)打印出来
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 5 |
| 400 |
| 400 |
| 400 |
+-----------+
最后,我得到了候选人: 4,5,400
将distinct
用于首要目标肯定是可行的
SELECT DISTINCT F2.FRIEND_ID
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1);
打印
+-----------+
| FRIEND_ID |
+-----------+
| 4 |
| 5 |
| 400 |
+-----------+
现在,需要通过相互计数进行排序。
下面是每个候选人的共同好友数量。
+-----------+
| FRIEND_ID |
+-----------+
| 4 | 1 (1 -> 2 -> 4)
| 5 | 2 (1 -> 2 -> 5, 1 -> 3 -> 5)
| 400 | 3 (1 -> 100 -> 400, 1 -> 200 -> 400, 1 -> 300 -> 400)
+-----------+
我如何计算和排序这些共同的朋友数量?
SELECT F2.FRIEND_ID, COUNT(*)
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID;
打印
+-----------+----------+
| FRIEND_ID | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 5 | 2 |
| 400 | 3 |
+-----------+----------+
这样啊,原来是这么回事!
SELECT F2.FRIEND_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F1.PLAYER_ID = 1
AND F2.FRIEND_ID != 1
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = 1)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
打印
+-----------+-----+
| FRIEND_ID | MFC |
+-----------+-----+
| 400 | 3 |
| 5 | 2 |
| 4 | 1 |
+-----------+-----+
有人能证实这一点吗?该查询是最优吗?当它成为一个视图时,有没有可能出现性能问题?
谢谢。
更新
我创建了一个视图
CREATE VIEW FOLLOWABLE AS
SELECT F1.PlAYER_ID, F2.FRIEND_ID AS FOLLOWABLE_ID, COUNT(*) AS MFC
FROM FRIEND F1 INNER JOIN FRIEND F2 ON F1.FRIEND_ID = F2.PLAYER_ID
WHERE F2.FRIEND_ID != F1.PLAYER_ID
AND F2.FRIEND_ID NOT IN (SELECT FRIEND_ID FROM FRIEND WHERE PLAYER_ID = F1.PLAYER_ID)
GROUP BY F2.FRIEND_ID
ORDER BY MFC DESC;
并经过测试。
mysql> select * from FOLLOWABLE;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 2 | 100 | 1 |
| 2 | 200 | 1 |
| 2 | 300 | 1 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
6 rows in set (0.01 sec)
mysql> select * from FOLLOWABLE WHERE PLAYER_ID = 1;
+-----------+---------------+-----+
| PlAYER_ID | FOLLOWABLE_ID | MFC |
+-----------+---------------+-----+
| 1 | 400 | 3 |
| 1 | 5 | 2 |
| 1 | 4 | 1 |
+-----------+---------------+-----+
3 rows in set (0.00 sec)
发布于 2012-06-11 21:35:59
使用此编辑
SELECT `friend_id` AS `possible_friend_id`
FROM `friends`
WHERE `player_id` IN ( --selecting those who are known
SELECT `friend_id` --by freinds of #1
FROM `friends`
WHERE `player_id` = 1)
AND `friend_id` NOT IN ( --but not those who are known by #1
SELECT `friend_id`
FROM `friends`
WHERE `player_id` = 1)
AND NOT `friend_id` = 1 --and are not #1 himself
--if one is known by multiple people
--he'll be multiple time in the list
GROUP BY `possible_friend_id` --so we group
ORDER BY COUNT(*) DESC --and order by amount of repeatings
https://stackoverflow.com/questions/10980112
复制相似问题