sql-左联接-计数 [英] sql - left join - count

问题描述

假设我有两个表.文章和评论.

suppose i have two tables. articles and comments.

当我从articles表中选择列时，我也想在同一条select语句中选择该文章的评论数...(假设这两个表之间的公共字段是articleid)

when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid)

我该怎么做?我可以完成它，但是我不知道我的方法是否会有效，所以我想学习正确的方法.

how do I do that? I can get it done, but I do not know if my way would be efficient, so i want to learn the right way.

推荐答案

使用:

   SELECT a.articleid, 
          COUNT(*) AS num_comments
     FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
 GROUP BY a.articleid

无论您要从ARTICLES表中获取什么列，都必须在GROUP BY子句中进行定义，因为它们没有对它们执行的聚合函数.

Whatever columns you want from the ARTICLES table, you'll have to define in the GROUP BY clause because they aren't having an aggregate function performed on them.

这篇关于sql-左联接-计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！