MySQL 选择另一个表上的计数查询

Question

我有一个包含三个表的简单文章应用程序:

article 
id, title,  body,   user_id

comment
id, article_id, user_id,    body

user
id, username

在着陆页上，我想显示最新的文章标题以及文章的作者姓名和评论总数。主要问题是如何获取文章的评论总数，我没弄对。我应该得到以下输出:

title           username    total_comments
article 2       user2           0
article 1       user1           2

在我的实际应用中，我在文章表中添加了一列，用于记录文章的评论总数。当向系统添加新评论时，此栏会更新。这样做的问题是当添加新评论时文章表被锁定。在我的应用程序中，每分钟都会添加很多评论。所以我试图通过重写 SQL 查询来避免锁定文章表。

这里是一些测试数据:

CREATE TABLE `article` (
`id` INT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 100 ) NULL ,
`body` LONGTEXT NULL ,
`user_id` INT NULL
) ENGINE = MYISAM ;


CREATE TABLE `comment` (
`id` INT NULL AUTO_INCREMENT PRIMARY KEY ,
`article_id` INT NULL ,
`user_id` INT NULL ,
`body` LONGTEXT NULL
) ENGINE = MYISAM ;

CREATE TABLE `user` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;


INSERT INTO `test`.`user` (
`id` ,
`username`
)
VALUES (
NULL , 'user1'
), (
NULL , 'user2'
);


INSERT INTO `test`.`article` (
`id` ,
`title` ,
`body` ,
`user_id`
)
VALUES (
NULL , 'article 1', 'body article 1', '1'
), (
NULL , 'article 2', 'body article 2', '2'
);

INSERT INTO `test`.`comment` (
`id` ,
`article_id` ,
`user_id` ,
`body`
)
VALUES (
NULL , '1', '1', 'body comment to article 1'
), (
NULL , '1', '1', 'body comment to article 1'
);

Answer 1

SELECT a.title AS title, u.username AS username, count(c.id) as total_comments FROM articles a
   LEFT JOIN comments c ON c.article_id = a.id
   LEFT JOIN users u ON a.user_id = u.id
GROUP BY a.id