MySQL选择查询以获取当前用户帖子和关注的朋友帖子

小编典典

MySQL选择查询以获取当前用户帖子和关注的朋友帖子

sql

我当前的选择查询语句：

 SELECT
    T.postID, 
    T.message, 
    T.time,
    U.userID,
    U.name, 
    U.username, 
    U.picture_url
 FROM
    post AS T,
    users AS U 
 WHERE
    T.postID = '$uid' //$uid holds the id of the current logged in user
    order by T.postID DESC;

我的选择查询输出当前登录用户的帖子，但我也要输出当前登录用户的关注用户的帖子，我该怎么做？

用户表：

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| userID     |int(11)     | NO   | PRI | NULL    | auto_increment |
| name       |VARCHAR(60) | NO   |     | NULL    |                |
| username   |VARCHAR(20) | NO   |     | NULL    |                |
| picture_url|VARCHAR(200)| NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

开机自检表：

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| postID     |int(11)     | NO   | PRI | NULL    | auto_increment |
| pUserID    |int(11)     | NO   |     | NULL    |                |
| message    |VARCHAR(140)| NO   |     | NULL    |                |
| time       |datetime    | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

遵循用户表

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| fid        |int(11)     | NO   | PRI | NULL    | auto_increment |
| userId1    |int(11)     | NO   |     | NULL    |                |
| userId2    |int(11)     | NO   |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+

阅读 196

2021-05-16

共1个答案

小编典典

首先，建议在隐式CROSS
JOIN上使用显式JOINS

请尝试以下查询。

 SELECT
    T.postID, 
    T.message, 
    T.time,
    U.userID,
    U.name, 
    U.username, 
    U.picture_url,
    F.userID2,
 FROM
    users AS U  
 INNER JOIN 
    follow_user AS F
 ON U.userID = F.userId1    
 INNER JOIN 
    post AS T
 ON T.pUserID = U.userID OR T.pUserID = F.userId2 
 WHERE
    U.userID = '$uid' //$uid holds the id of the current logged in user
    order by T.postID DESC;

2021-05-16