MySQL选择与另一个表上的计数查询 [英] MySQL select with a count query on another table

问题描述

我有三个表的简单文章应用程序：

I have simple article application with three tables:

article 
id, title,  body,   user_id

comment
id, article_id, user_id,    body

user
id, username

在目标网页上，我想显示包含作者姓名和文章评论总数的最新文章标题。主要的问题是如何获得文章的评论的总数，我没有得到它的权利。我应该得到以下输出：

On the landing page, I want to show the latest article titles with the author name and total numbers of comments of the article. The main problem is how to get total numbers of comments of the article,I did not get it right. I should get the following output:

title           username    total_comments
article 2       user2           0
article 1       user1           2

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

In my real application, I added a column in article table for total number of comments to the article. this column is updated when a new comment is added to the system. The problem with this is that the article table is locked when a new comment is added. In my application a lot of comments are added every minutes. So I am trying to avoid locking article table with re-writing the SQL query.

这里是一些测试数据：

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'
);

推荐答案

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

这篇关于MySQL选择与另一个表上的计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！