SQL Query加入帖子，每个帖子的帖子数量和每个帖子的所有照片？

Simple Question. More Complex Answer. How can I join my Posts table with my Posts_Pictures table with my Users_Favorites table to get all the posts, the numberOfFollowers for each post, and finally if the user is following that specific post?

简单的问题。更复杂的答案。如何使用我的Posts_Pictures表和我的Users_Favorites表加入我的Posts表以获取所有帖子,每个帖子的numberOfFollowers,最后如果用户正在关注该特定帖子?

Here are my tables:

这是我的表格:

Posts:

postId INT

fullName VARCHAR

etc...

Users:

userId INT

etc...

Post_Pictures:

postPictureId INT

image VARCHAR

idx INT

post_id INT

Users_Favorites:

userFavoritesId INT

user_id INT

post_id INT

This is what I have so far and it returns everything except the total number of followers per post and the pictures associated with each post:

这就是我到目前为止所做的一切,除了每个帖子的关注者总数以及与每个帖子相关的图片之外,它还会返回所有内容:

SELECT p.*
     , uf.`userFavoritesId` 
  FROM Posts as p
  LEFT 
  JOIN Users_Favorites as uf
    ON p.`postId` = uf.`post_id`
 ORDER 
    BY p.created DESC;

1 个解决方案

#1

-1

Try running only the below sub-query to see if the joins come back as you expect. If it looks ok, try running the entire thing.

尝试仅运行以下子查询以查看联接是否按预期返回。如果它看起来不错,请尝试运行整个事情。

This would return the postid from posts, the postpictureid from post_pictures, and it's counting the number of times for each post_id that there is a userfavoritesid that is not null.

这将返回帖子中的postid,post_pictures中的postpictureid,并且它计算每个post_id的次数,即userfavoritesid不为null。

Providing additional info or some sample data will make it much easier to provide a better answer.

提供其他信息或一些示例数据可以更容易地提供更好的答案。

select postid
    , postpictureid
    , sum(case when userfavoritesid is not null then 1 else 0 end) as numberoffollowers
from
    (
    select p.postid
        , p.fullname
        , pp.postpictureid
        , pp.image
        , pp.idx
        , uf.userfavoritesid
        , uf.user_id
        , uf.post_id
    from posts as p
    left join post_pictures as pp
        on a.postid = pp.post_id
    left join users_favorites as uf
        on pp.post_id = uf.post_id
    ) as x
;

#1

-1