MySQL - "Most social User"在多个表中评论最多

Question

我有 2 个关注表 - 'videoComments'、'storyComments'。

我需要在 videoComments 和 storyComments 中找到条目最多的“posterID”。这是我目前的代码，但它只调用 videoComments:

$sql = "SELECT (SELECT posterID 
                  FROM videoComments 
              GROUP BY posterID 
              ORDER BY COUNT(posterID) DESC LIMIT 1) ) AS mostSocialUser ";

我如何提取它并比较两个表中 posterID 的数量？

Answer 1

使用:

   SELECT x.posterid,
          COUNT(y.posterid) + COUNT(z.posterid) AS numComments
     FROM (SELECT vc.posterid
             FROM VIDEOCOMMENTS vc
           UNION 
           SELECT sc.posterid
             FROM STORYCOMMENTS sc) x
LEFT JOIN VIDEOCOMMENTS y ON y.posterid = x.posterid
LEFT JOIN STORYCOMMENTS z ON z.posterid = x.posterid
 GROUP BY x.posterid
 ORDER BY numComments DESC
    LIMIT 1