需求:
表1,文章表,
表2,评论表,
第一种实现方式:
SELECT a.id,a.title,a.content,a.type_id,count(b.id) as d from message as a LEFT JOIN comment as b on a.id=b.id GROUP BY b.id ORDER BY d desc
第二种实现方式:
SELECT a.id,a.title,a.content,a.type_id,if(ISNULL(c)=1,0,c) as d from message as a LEFT JOIN
(SELECT id,count(id) as c from comment GROUP BY id) as b
on a.id=b.id ORDER BY c desc
(SELECT id,count(id) as c from comment GROUP BY id) as b
on a.id=b.id ORDER BY c desc
结果: