mysql - 设置交叉连接子句 (sql)

Question

我希望显示共同好友功能，但我的用户好友列表存储在另一个表上。

我想知道是否可以使用每一行的数据作为连接B的子句

SELECT * FROM (  SELECT * FROM `users`) a
CROSS JOIN (SELECT COUNT(*) AS MUTUAL_FRIENDS  
FROM `user_friends` WHERE `friend_to` = `a`.`userid`)  b
ORDER BY `b`.`MUTUAL_FRIENDS` ASC

我想要做的是使用用户表中的userid作为连接的子句:

`friend_to` = `a`.`userid`

预期结果:

+--------=+---------+-----------+--------------+
| userid  | Username |Photo_URL |MUTUAL_FRIENDS|
+---------+---------+-----------+--------------+
   1       Somename1  /image1.png      3
   4       Somename4  /image4.png      2
   2       Somename2  /image2.png      1
   3       Somename3  /image3.png      0
   5       Somename5  /image5.png      0

好友表:

+--------=+---------+-----------+
| friend_id | userid  |friend_to |
+---------+---------+-----------+
   1         1          2
   2         2          1
   3         1          3
   4         3          1
   5         1          4
   6         4          1  

用户表

+--------=+---------+-----------+
| userid  | Username |Photo_URL |
+---------+---------+-----------+
   1       Somename1  /image1.png
   2       Somename2  /image2.png
   3       Somename3  /image3.png
   4       Somename4  /image4.png
   5       Somename5  /image5.png

或者我这样做完全错误吗？

Answer 1

这是一种方法。

使用交叉连接

SELECT u.userid,u.Username,u.Photo_URL,IFNULL(Num,0) MUTUAL_FRIENDS
FROM Users u
LEFT JOIN (SELECT userid, COUNT(*) Num
FROM (SELECT a.userid
FROM Friends a
CROSS JOIN Friends b
WHERE a.userid = b.friend_to and a.friend_to = b.userid) r
GROUP BY userid) k ON u.userid = k.userid
ORDER BY MUTUAL_FRIENDS DESC, u.userid

没有交叉连接

SELECT u.userid,u.Username,u.Photo_URL,IFNULL(Num,0) MUTUAL_FRIENDS
FROM Users u
LEFT JOIN (SELECT userid, COUNT(*) Num
FROM (SELECT a.userid 
FROM Friends a, Friends b
WHERE a.friend_to = b.userid and b.friend_to = a.userid) r
GROUP BY userid) k ON u.userid = k.userid
ORDER BY MUTUAL_FRIENDS DESC, u.userid

我会尽力一点点解释，如有错误请指正。

1. 条件a.friend_to = b.userid且b.friend_to = a.userid是用于仅选择共同的 friend 。那么如果用户有这样的情况“相互连接”，对应的userid会出现一次。

2. 获取计数以及用户 ID。

3. 左连接userid和count表。

结果

userid  Username    Photo_URL   MUTUAL_FRIENDS
1   Somename1   /image1.png 3
2   Somename2   /image2.png 1
3   Somename3   /image3.png 1
4   Somename4   /image4.png 1
5   Somename5   /image5.png 0

根据数据，user1 是 user2、user3 和 user4 的共同好友。用户2只是用户1的共同好友，用户3和用户4也是如此。

With CROSS JOIN SQL Fiddle

Without CROSS JOIN SQL Fiddle