SQL选择您可能认识的人
The question you're asking appears subjective and is likely to be closed.
当我填写标题字段时，当我看到上面的可怕警告时，我并不感到惊讶。
我读过几乎每个关于friends of friends或mutual friends的帖子，但我不确定我是否找到了我想做的正确的解决方案。
对不起，我英语不好，SQL也不好。
我怎么才能找到正确的答案，而不是两种语言都好呢？
我决定我得问一问。我不会为down-votes或任何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这两个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.。
- 当两个人都说他们认识对方时，他们可以说成acquaintance.
现在，我想知道有没有什么好办法
选择其他给定的玩家( PLAYER_IDs
- With )(例如1)每个都是给定玩家的直接friends'
- Which的朋友之一的
- 每个不是玩家本人(不包括1个-> 2 -> 1)
- 每个不是玩家的直接朋友(不包括3个-> 2 -> 3 by 1 -> 3)
- 按共同好友的数量排序。
我认为贾斯汀·尼斯纳在"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)https://stackoverflow.com/questions/10980112
复制相似问题