所以我正在开发一个拥有大量多对数据库关系的网站,因为我对查询关系数据库还有点新意,所以我有点挣扎.所以我有几个表,我试图从中获取相关数据.我不会进入整个数据库,因为它非常大,我试图获得所有特定用户帖子的评论数量和帖子的喜欢数量,以及我的方式关于它是这样使用LEFT JOIN
SELECT Post.idPosts, Post.Title, Post.Date_poste,
COUNT(Post_has_Comments.Post_idPost),
COUNT(Post_has_Likes.Post_idStories)
FROM Post
LEFT JOIN Post_has_Comments ON Post.idPost = S
Post_has_Comments.Post_idStories
LEFT JOIN Post_has_Likes ON Post.idPost = Post_has_Likes.Post_idStories
WHERE Post.idUsers = 1
但我遇到的问题是,如果没有评论或不喜欢这将返回错误,此外如果有类似或评论它将返回两个字段中的最高数字,例如,如果有3条评论在一个帖子和1之类的它也将在类似字段中返回3,因为它正在计算它返回的行数我猜.所以我的问题是如何实际获得喜欢和评论的真实数量并放入该字段,并且如果没有错误而不是错误则返回0?
解决方法:
SELECT Post.idPosts, Post.Title, Post.Date_poste,
coalesce(cc.Count, 0) as CommentCount,
coalesce(lc.Count, 0) as LikeCount
FROM Post p
left outer join(
select Post_idPost, count(*) as Count
from Post_has_Comments
group by Post_idPost
) cc on p.idPost = cc.Post_idPost
left outer join (
select Post_idStories, count(*) as Count
from Post_has_Likes
group by Post_idStories
) lc on p.idPost = lc.Post_idStories
WHERE p.idUsers = 1
标签:php,sql,mysql,join
来源: https://codeday.me/bug/20190726/1539591.html