开发者

MySQL - "Most social User" with the most comments in multiple tables

I have 2 tables of concern - 'videoComments', 'storyComments'.

I need to find the 'posterID' that has the most entries in videoComments and storyComments. Here's the code I have so far, but it only calls videoComments:

$sql = "SELECT (SELECT posterID 
                  FROM videoComments 
              G开发者_JAVA百科ROUP BY posterID 
              ORDER BY COUNT(posterID) DESC LIMIT 1) ) AS mostSocialUser ";

How do I pull it and compare the COUNT of posterID from both tables?


Use:

   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


Try this:

SELECT (
    SELECT posterID FROM (
        SELECT posterID FROM videoComments 
        UNION 
        SELECT posterID FROM storyComments
    ) GROUP BY posterID 
      ORDER BY COUNT(posterID) DESC LIMIT 1
) AS mostSocialUser
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜