php - MySQL 查询对 GROUP BY 3 表计数中的结果进行排序

Question

我正在编写一个列表系统，并尝试根据 2 个表中的评论和投票数对帖子进行排序。

Table1 : Lists => id, title, detail

Table2 : Votes => voteid, listid

Table3 : Comments => commentid, listid

我当前的查询在哪里

$q = mysql_query("SELECT * FROM zoo_leads 
LEFT JOIN Votes ON Lists.id=Votes.listid
LEFT JOIN Comments ON Lists.id=Comments.listid
GROUP BY Lists.id ORDER BY Comments.listid DESC LIMIT 10

它完美地向我显示了结果，但 ORDER BY 是 Lists.id 而不是投票数和评论数

Answer 1

尝试:

SELECT *
FROM   zoo_leads
       LEFT JOIN votes
         ON lists.id = votes.listid
       LEFT JOIN comments
         ON lists.id = comments.listid
GROUP  BY lists.id
ORDER  BY COUNT(votes.id) DESC,
          COUNT(comments.id) DESC
LIMIT  10