列出mysql中注释最多的新闻

Question

我有两个桌面新闻和评论：

新闻：newsId，newsTitle，newsDate，newsReadCount

注释：ID、newsID、注释、日期

我选择了10行，根据两个日期之间的值newsReadCount，这些行具有最大读取量：

SELECT * FROM news WHERE DATE(newsDate) BETWEEN '20120414' AND '20130414' ORDER BY newsReadCount DESC LIMIT 0, 10

在这里，我想选择10个新闻与newsTitle，总评论计数从两个日期之间的新闻表有最大的评论基于关系newsID的评论表。如何在mysql中查询这个问题？我需要加入吗。请帮助我，我是mysql的新手。

注释表的newsID是新闻表的新闻ID主键的值。

编辑：

选择两个日期之间的新闻，其结果如下

结果：

| newsTitle  | Total Comments |     
-------------------------------    
| onenews    |  25            |    
| another    |  30            |

Answer 1

你需要join，group by，order by和limit

SELECT
    news.newsTitle, COUNT(comments.ID) AS `Total Comments`
FROM news INNER JOIN comments ON
    news.newsId = comments.newsID AND news.newsDate BETWEEN '20120414' AND '20130414'
GROUP BY news.newsId
ORDER BY `Total Comments` DESC
LIMIT 10;

请见sql小提琴。

Answer 2

我想你可以用

SELECT n.newsTitle, count(c.ID) as totalCount
FROM   News n LEFT JOIN Comments c ON n.newsId = c.newsID
WHERE  DATE(n.newsDate) BETWEEN '20120414' AND '20130414'
GROUP BY n.newsTitle
ORDER BY totalCount DESC
LIMIT   10