MySQL 选择带有评论的表(即使有空字段)

Question

我需要从表格中计算评论数量和平均成绩client_reviews。并将这两个字段添加到表中client_impression。此MySQL重新访问正在解决仅显示评论字段的问题。但是即使没有评论，我也需要显示所有字段。怎么做？

SELECT `impression`.*, review.count, review.grade FROM `client_impression` AS `impression` 
JOIN (SELECT impression_id, count(id) AS `count`, SUM(stars) AS `grade` 
FROM `client_reviews` AS rev
WHERE rev.status=1 AND (SELECT city_id FROM client_impression_offer WHERE id=rev.impression_offer_id) = 1
GROUP BY rev.impression_id  ) AS review ON review.impression_id = `impression`.`id`

WHERE `impression`.`city_1`=1 AND `impression`.`id` IN (SELECT `impression` FROM `impression_to_rank` WHERE `rank`=6)

Answer 1

使用左连接

selct `impression`.*, review.count, review.grade FROM `client_impression` AS `impression` 
left join
(
SELECT impression_id, count(id) AS `count`, SUM(stars) AS `grade` 
FROM `client_reviews` AS rev WHERE rev.status=1 and
(SELECT city_id FROM client_impression_offer WHERE id=rev.impression_offer_id) = 1
group by impression_id
) review ON review.impression_id = `impression`.`id` and
`impression`.`city_1`=1 AND `impression`.`id` IN (SELECT `impression` FROM `impression_to_rank` WHERE `rank`=6)