mysql - 从 LEFT OUTER JOIN 计算 NULL 值，这可能吗？

Question

我有一个与加入相关的问题，如果有人可以帮忙

我有一个疑问:

SELECT cl.*,c.*,s.*,o.*,p.*,r.*
FROM COMPANY_LOCATION__K cl
INNER JOIN COMPANY__K c on c.COMPANY_ID__K = cl.COMPANY_ID__K
INNER JOIN SOCIAL_MEDIA__K s on cl.COMPANY_ID__K = s.COMPANY_ID__K
INNER JOIN OPERATIONAL_HOURS__K o on cl.LOCATION_ID__K = o.LOCATION_ID__K
INNER JOIN PERMISSIONS__K p on cl.LOCATION_ID__K = p.LOCATION_ID__K
LEFT OUTER JOIN REVIEW__K r on cl.LOCATION_ID__K = r.LOCATION_ID__K
ORDER BY DISTANCE__K LIMIT 100;

这将列出有关公司的信息和许多其他统计信息。

我遇到了麻烦，因为 Company_Location 可能不一定有与之关联的评论，所以当我将 r.* 更改为 COUNT( r. rating)，没有评论的行不会显示在我的结果集中。我希望计数返回为 0，这样我就知道不存在评分。这可能吗？

提前致谢

Answer 1

我相信您需要一个group by来完成您的需要。如果您想要每个公司的信息，我认为应该是:

SELECT cl.*, c.*, s.*, o.*, p.*, count(r.rating)
FROM COMPANY_LOCATION__K cl
INNER JOIN COMPANY__K c on c.COMPANY_ID__K = cl.COMPANY_ID__K
INNER JOIN SOCIAL_MEDIA__K s on cl.COMPANY_ID__K = s.COMPANY_ID__K
INNER JOIN OPERATIONAL_HOURS__K o on cl.LOCATION_ID__K = o.LOCATION_ID__K
INNER JOIN PERMISSIONS__K p on cl.LOCATION_ID__K = p.LOCATION_ID__K
LEFT OUTER JOIN REVIEW__K r on cl.LOCATION_ID__K = r.LOCATION_ID__K
GROUP BY cl.LOCATION_ID
ORDER BY DISTANCE__K
LIMIT 100;