我也想看看零的计数

Question

我还想在这个sql中显示零的计数。

SELECT diaries.id, emotion_id, image_url, color, summary, public, is_completed, diaries.created_at, diaries.is_deleted, likes.is_deleted, users.nickname, likes.diary_id, COUNT(likes.user_id) AS cnt FROM diaries
INNER JOIN emotions ON diaries.emotion_id = emotions.id
INNER JOIN users ON diaries.user_id = users.id
LEFT OUTER JOIN likes ON diaries.id = likes.diary_id
WHERE diaries.is_deleted = 0 AND public = 1 AND is_completed = 1 AND likes.is_deleted = 0
GROUP BY diary_id
ORDER BY cnt DESC

我也想看看零的计数。。。。。

Answer 1

where子句不能包含表中使用LEFT JOIN的任何字段。这会导致LEFT JOIN转换为INNER JOIN。

您需要将此子句移到LEFT JOIN语句中：

AND likes.is_deleted = 0

这将返回一个COUNT(赞数=0(