The question you're asking appears subjective and is likely to be closed.
看到上面的内容我并不惊讶可怕的警告当我填写标题字段时。
我读了几乎所有讨论的话题friends of friends or mutual friends但我不确定我是否找到了我想要的正确解决方案。
抱歉，我不擅长英语，也不擅长 SQL。
如果我不擅长两种语言，如何才能找到正确的答案？
我决定我必须问一下。我不会让自己失望down-vote或任何duplication warnings.
因为我想要答案，所以我会尽可能真诚地写下来，以便以后有类似的问题可以得到帮助。
我有一张朋友关系表。
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
Both composite primary keys也是来自的外键PLAYER table.
我问了这些好人，并得到了“人们彼此认识”的回答。
从表中了解 SQL 视图 https://stackoverflow.com/questions/10976568/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
您可能会注意到，这种关系的业务逻辑有以下两个目的。
- 一名玩家可以说他或她认识其他人。
- 当两个人都说认识的时候，就可以说是熟人了。
现在我想知道有什么好的办法吗
- 选择其他 PLAYER_ID
- 对于给定的 PLAYER(PLAYER_ID)（比如 1）
- 其中每个都是“给定玩家的直接朋友的朋友”之一
- 其中每一个都不是玩家本人（不包括 1 -> 2 -> 1）
- 其中每一个都不是玩家的直接好友（从 1 -> 2 -> 3 by 1 -> 3 中排除 3 个）
- 如果可能的话，按共同朋友的数量排序。
我认为贾斯汀·尼斯纳的回答是“你可能认识的人”sql查询 https://stackoverflow.com/questions/4147482/people-you-may-know-sql-query是我必须遵循的最近的路径。
提前致谢。
如果这个主题确实重复并且没有必要，我将关闭该线程。
更新 - - - - - - - - - - - - - - - - - - - - - - - - - -------------
拉斐尔·阿尔托斯的评论whose name is same with my future daughter（这是男孩的名字吗？），
3 是候选人friends of friends of 1 because
1 knows 2
2 knows 3
但排除了，因为
1 already knows 3
基本上我想为given player the
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
prints
+-----------+-----------+-----------+-----------+
| 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
prints
+-----------+
| 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;
prints
+-----------+
| FRIEND_ID |
+-----------+
| 1 |
| 3 |
| 4 |
| 5 |
| 5 |
+-----------+
not 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;
prints
+-----------+
| 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);
prints
+-----------+
| 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
Putting 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);
prints
+-----------+
| 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;
prints
+-----------+----------+
| 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;
prints
+-----------+-----+
| 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)
