mysql - 如何使用 SELECT 列作为使用 MySQL 的 WHERE 子句获取另一个表的行数

Question

我正在从 MySQL 数据库的“帖子”表中选择帖子，但我希望返回的其中一列是评论表中使用所选帖子 ID 的行的评论计数。我的数据库是关系型的，所以评论表有一个帖子 ID 字段 (comment_post_id)。这是我所拥有的，但此查询在 PHPMYAdmin 中造成语法错误:

SELECT post_id, post_title, post_status, post_image, post_tags, post_views, post_date, user_name, cat_title, 
COUNT(SELECT comment_id FROM comments WHERE comment_post_id = post_id) AS comment_count
FROM posts 
LEFT JOIN users ON post_author = user_id 
LEFT JOIN categories ON post_category_id = cat_id
ORDER BY post_id DESC

帖子和评论表列的布局如下:

发布:post_id, post_title, post_status, post_image, post_tags, post_views, post_date, post_author, post_category_id

评论:comment_id, comment_post_id, comment_content

Answer 1

您可以在此处使用此查询来获取计数。试试这个方法

SELECT post_id, post_title, post_status, post_image, post_tags, post_views, post_date, user_name, cat_title, 
(SELECT COUNT(comment_id) FROM comments WHERE comment_post_id = post_id) AS comment_count
FROM posts 
LEFT JOIN users ON post_author = user_id 
LEFT JOIN categories ON post_category_id = cat_id
ORDER BY post_id DESC