我有两个表，例如：
帖子：
id | author | content | date
1 | Lucas | Hello! | 2016
2 | Igor | Hi! | 2016
注释：
id | post_id | content | date
1 | 2 | hehehe | 2016
2 | 1 | hahaha | 2016
3 | 2 | huhuhu | 2016
我要做一个SELECT，返回所有帖子和COUNT行所有带有post.id = comment.id的评论.
所以，我尝试了：
SELECT p.id, p.author, p.content, p.date, COUNT(*) AS numComments FROM post p LEFT JOIN comment ON p.id = post_id WHERE p.author = '$author' GROUP BY p.id DESC LIMIT 12
我必须做到。但是，即使不存在p.id = post_id注释，他也返回 1。
所以，我尝试了：
SELECT p.id, p.author, p.content, p.date, CASE WHEN COUNT(*) < 1 THEN '0' ELSE COUNT(*) END AS numComments FROM post p LEFT JOIN comment ON p.id = post_id WHERE p.author = '$author' GROUP BY p.id DESC LIMIT 12
但结果是一样的。怎么做？
由于外连接会返回一行，即使没有匹配的数据，您也需要计算内部表中的一列，通常是连接中使用的列：
SELECT p.id, p.author, p.content, p.date, COUNT(post_id) AS numComments
FROM post p LEFT JOIN comment ON p.id = post_id
WHERE p.author = '$author'
GROUP BY p.id -- seems to be mysql, otherwise you need to add more columns to the list
如果您不想显示具有零计数的行，只需切换到内部联接。
你可以通过这种方式获得计数，最后也是排序而不是分组：
SELECT p.id, p.author, p.content, p.date,
(select COUNT(*) from comment where p.id = comment.post_id) AS numComments FROM post p
WHERE p.author = '$author'
ORDER BY p.id DESC LIMIT 12