mysql - 使用 Count 或 Sum 在 MYSQL 中加入

Question

我正在尝试使用连接执行查询。如果我使用“nil”作为 numcomments，我会得到所有的故事，但是如果我尝试用 COUNT 任何东西来计算它们，它会将故事限制为只有带有评论的故事.我想要的是取回所有故事，但如果计数为零则给零

这是架构

//stories
id|story
1|Microsoft Announces Earnings
2|Apple Inks Content Deal
3|Google In Talks With Nissan
4|Netflix Greenlights Series

//comments
id|storyid|comment
1|1|Not what I would have expected
2|1|Look at numbers for Azure
3|1|I called this
4|3|Who cares

Here is the query:
This only returns the stories with commments ie stories 1 and 3

$sql = "
SELECT COUNT(c.comment) numcomments
     , s.story event
     , s.id
     , c.storyid   
  FROM stories s
  LEFT 
  JOIN comments c
    ON c.storyid = s.id 
";

//this returns all the stories but does not give me a count of comments for obvious reasons

$sql = "
SELECT 'nil' numcomments
     , s.story event
     , s.id
     , c.storyid   
  FROM stories s
  LEFT 
  JOIN comments c
    ON c.storyid = s.id 
";

我怎样才能得到所有的故事，但如果没有评论，就在计数或 numcomments 字段中得到一个零

Answer 1

尝试以下查询:

SELECT s.id, s.story, count(c.id) as NumOfComments 
FROM stories s LEFT JOIN comments ON  s.id=c.storyid 
GROUP BY s.id, s.story