Mysql queries the number of comments, mysql queries comments

Mysql queries the number of comments, mysql queries comments

As a Tom, this problem has been solved for half a day and is recorded here.

Two tables: Article table and comment table

Article table (article): id

Comment table (comment): id, c_aid

Requirement: query the number of all articles and comments and display them in descending order (0 is returned for the number of comments of articles without comments)

SQL: select. id, ifnull (AC. comment_count, 0) as cont from B _articleinfo a left join (select c_aid, count (1) as comment_count from B _article_comment group by c_aid) AC ON. id = AC. c_aid order by. id desc

Tip: because the number of queried comments does not have a corresponding ing, you need to add attributes to the xml of model and mybatis to get the data.