php - 从2个表中选择行数并合并为一行(mysqli)

我用php和mysqli创建了一个类似facebook的web应用程序
在我的应用程序中，我有一个帖子表、一个喜欢的帖子表和一个评论表
我想用他的帖子id得到一行中每个帖子的评论和喜欢的数量！!!
我试过一些像这样的问题：

select `tblpost`.`post_id`,  COALESCE(TCOMM.`comment_num`,0) as `c_num`, COALESCE(TLIKE.`like_num`,0) as `l_num`
from
(select `tblpost`.`post_id`, count(*) as `like_num` from `tblpost` join `tbllikes` on `tbllikes`.`post_id` = `tblpost`.`post_id` group by `tblpost`.`post_id`
) TLIKE
inner join
(select `tblpost`.`post_id`, count(*) as `comment_num` from `tblpost` join `tblcomments` on `tblcomments`.`post_id` =  `tblpost`.`post_id` group by `tblpost`.`post_id`) TCOMM
on
    TCOMM.`post_id` = TLIKE.`post_id`

但我不知道我有什么问题

最佳答案

你可以用两个左连接来计算距离。
如果表like_id和comment_id中有tbllikes和tblcomments字段，则类似这样的操作将起作用

SELECT
    tblpost.post_id AS post_id,
    COUNT(DISTINCT tbllikes.like_id) AS likes,
    COUNT(DiSTINCT tblcomments.comment_id) AS comments
FROM tblpost
LEFT JOIN tbllikes ON tbllikes.post_id = tblpost.post_id
LEFT JOIN tblcomments on tblcomments.post_id =  tblpost.post_id
GROUP BY tblpost.post_id

关于php - 从2个表中选择行数并合并为一行(mysqli)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26573128/