如何在MySQL中关联帖子查询结果与评论数统计结果？

我有三张表：Users、Posts、Comments。

目前我用以下SQL查询所有帖子的信息：

SELECT
    Posts.id as postId,
    Users.id as authorId,
    Posts.title,
    Users.displayName,
    Posts.createdAt
FROM
    Users
INNER JOIN
    Posts
ON
    Users.id = Posts.authorId;

执行后返回结果：

+--------+----------+---------+-------------+---------------------+
| postId | authorId | title   | displayName | createdAt           |
+--------+----------+---------+-------------+---------------------+
|      1 |        1 | title 1 | Alice       | 2022-07-22 16:35:39 |
|      2 |        2 | title 2 | Bob         | 2022-07-22 16:35:47 |
+--------+----------+---------+-------------+---------------------+

我希望把上面的结果和下面这条SQL的执行结果关联起来：

SELECT postId, COUNT(*) FROM Comments GROUP BY postId

该语句返回结果：

+--------+----------+
| postId | COUNT(*) |
+--------+----------+
|      1 |        5 |
|      2 |        3 |
+--------+----------+

想要得到的最终结果如下：

+--------+----------+---------+-------------+---------------------+--------------+
| postId | authorId | title   | displayName | createdAt           | commentCount |
+--------+----------+---------+-------------+---------------------+--------------+
|      1 |        1 | title 1 | Alice       | 2022-07-22 16:35:39 | 5            |
|      2 |        2 | title 2 | Bob         | 2022-07-22 16:35:47 | 3            |
+--------+----------+---------+-------------+---------------------+--------------+

请问在MySQL中该如何编写对应的SQL语句？

你可以通过LEFT JOIN将原查询和评论统计的子查询关联起来，同时用COALESCE处理没有评论的帖子（确保这类帖子的commentCount显示为0而不是NULL）。最终SQL语句如下：

SELECT
    Posts.id as postId,
    Users.id as authorId,
    Posts.title,
    Users.displayName,
    Posts.createdAt,
    COALESCE(comment_stats.commentCount, 0) AS commentCount
FROM
    Users
INNER JOIN
    Posts ON Users.id = Posts.authorId
LEFT JOIN (
    SELECT postId, COUNT(*) AS commentCount 
    FROM Comments 
    GROUP BY postId
) AS comment_stats ON Posts.id = comment_stats.postId;

说明： #

1. 子查询comment_stats负责统计每个帖子的评论数量，并给统计结果起别名commentCount；
2. 使用LEFT JOIN而非INNER JOIN，能保证即使某个帖子没有任何评论，也会被包含在结果中，此时commentCount会被COALESCE转换为0；
3. 如果你的业务场景中所有帖子都一定有评论，用INNER JOIN也可行，但LEFT JOIN+COALESCE的写法更通用。

内容的提问来源于stack exchange，提问作者user13692250