mysql连接四个表并按desc排序结果计数

Question

我正在使用 Mysql 来获取热门帖子

我正在加入四个表 posts、posts_like、posts_shares、posts_comments 并区分它的 user_id。 (帖子点赞、分享和评论应添加到总数中)

我期待这个结果仅作为示例

RESULT:
post_id | user_id | count of shares, like, comments
---------------------------------------------------
  1     |     2    |      50                      |
  8     |     5    |      47                      |
---------------------------------------------------

表结构

table users
---------------------
id | username | etc |
---------------------

table posts
-------------------------------------
id |  user_id | content | datecreated
-------------------------------------

post_likes
--------------------------------
id | post_id | user_id         |
--------------------------------

post_shares
--------------------------------
id | post_id | user_id         |
--------------------------------

comments
---------------------------------------
id | post_id | user_id | description  |
--------------------------------------

user_id 应该不同

Answer 1

您可以使用左连接并按 p.id、p.user_id、u.username 计算基于不同组的数量

select p.id as post_id, p.user_id, u.username
    , count(distinct l.user_id)
    , count(distinct s.user_id)
    , count(distinct c.user_id)
    , ifnull(count(distinct l.user_id),0) + 
      ifnull(count(distinct s.user_id),0) + 
      ifnull(count(distinct c.user_id),0) total
from post p 
inner join users u  on u.id = p.user_id 
left join post_likes l on l.post_id = p.id 
left join post_shares s on s.post_id = p.id  
left join comments c on c.post_id = p.id 
group by  p.id, p.user_id, u.username
order by total desc