sql - Mysql SQL连接问题

Question

我正在尝试查找所有交易信息以及他们收到的评论数量。我的查询

select deals.*,
       count(comments.comments_id) as counts
from deals 
left join comments on comments.deal_id=deals.deal_id 
    where cancelled='N'

但现在它只显示至少有一条评论的交易。有什么问题吗？

Answer 1

您错过了 GROUP BY

select deals.*, count(comments.comments_id) as counts 
from deals 
left join comments on comments.deal_id=deals.deal_id 
where cancelled='N'
GROUP BY deals.deal_id