PHP mysql如何关联三个表，显示不同表的输出

我有这个图

我想做的就是输出:

您如何管理这一查询？

我有这个代码

SELECT users.firstname, users.lastname,
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name,
       posts.post_type, posts.date_posted
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
ORDER BY posts.pid DESC
//PROBLEM with this one is that it only views the post from all users.

//SO I added
SELECT COUNT(user_id) AS friends, SUM(user_id = ?) AS you, user_id
FROM feeds WHERE post_id = ?
//This one will give you two fields containing how many different users **feeds** the
post

请帮助大家。实际上，我只是关注 Facebook的“喜欢”状态
唯一的问题是，我不是从事此类工作的业余爱好者，因此，我很高兴听到您的所有答复。我真的需要你的帮助

最佳答案

如果我已正确理解您的要求，则希望与feeds表进行外部连接(以便即使没有关联的posts，也保留所有feeds)，然后是GROUP BY post.pid，以便将每个帖子的所有此类提要合并在一起，然后SELECT所需的信息。

我使用MySQL的 GROUP_CONCAT() 函数获取以逗号分隔的所有用户列表(最多 group_concat_max_len )，这些用户具有
给定帖子的“提要”(如果需要，可以使用SEPARATOR修饰符更改定界符)。

SELECT users.firstname, users.lastname,
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name,
       posts.post_type, posts.date_posted,
       COUNT(feeds.user_id) AS friends,   -- number of "likes"
       SUM(feeds.user_id = ?) AS you,     -- did I like this?
       GROUP_CONCAT(feeds.user_id)        -- who likes it?
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
GROUP BY posts.pid
ORDER BY posts.pid DESC

更新

要获得“顶过”该帖子(不包括自己)的用户的全名，需要再次加入users表:

SELECT users.firstname, users.lastname,
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name,
       posts.post_type, posts.date_posted,
       COUNT(feeds.user_id) AS friends,                      -- number of "likes"
       SUM(feeds.user_id = ?) AS you,                        -- did I like this?
       GROUP_CONCAT(
         CASE WHEN NOT likes.user_id = ? THEN                -- exclude self
           CONCAT_WS(' ', likes.firstname, likes.lastname)   -- full names
         END
       )
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
LEFT  JOIN website.users likes ON (feeds.user_id = likes.user_id)
GROUP BY posts.pid
ORDER BY posts.pid DESC