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
精彩评论