文章评论数 mysql_sql语句按评论数排序

先呈上我自己修改的sql语句

$sql = "select a.*,aa.id,count(aa.id) as countNum,c.catName,s.staffName

from __PREFIX__articles a left join __PREFIX__article_appraises aa on a.articleId=aa.articleId,__PREFIX__article_cats c,__PREFIX__staffs s

where a.articleFlag=1 and a.catId=c.catId and a.staffId = s.staffId GROUP BY a.articleId ";

if($catId>0){

$sql .= " and a.catId=$catId";

}

if(I('articleTitle')!='')$sql.=" and articleTitle like '%".$articleTitle."%'";

$sql.=' order by';

if($good>0){

$sql .= " good desc,";

}

if($bad>0){

$sql .= " bad desc,";

}

if($appraise>0){

$sql .= " countNum desc,";//根据评论数排序

}

$sql.=' articleId desc';

下面是网上的知识点：

SELECT

posts.id,

posts.content,

comments.id AS comment_id,

comments.body,

count(comments.id)

FROM posts

LEFT JOIN comments --左连接,保证左表posts中没有评论的文章也显示出来

ON posts.id = comments.post_id

GROUP BY posts.id --分组,配合count(comments.id)统计到每篇文章的评论数

ORDER BY count(comments.id) DESC, posts.id DESC --按评论数排序,相同则按文章ID排序

LIMIT 2 OFFSET (3-1)*2 --分页,2表示每页显示的文章数量,3表示第3页

下面是知识点二

1,在文章表里面加个字段叫评论数，然后每次有这篇文章新的评论就同时把它加1，然后根据这个字段排序即可

2,如果只拿文章ID的排序，可以在评论表里面这样做：

select post_id from comment group by post_id order by count(1) desc limit 0, 10;

其中post_id是文章ID，comment是评论表，这个语句拿到评论数最多的前10篇文章ID