SQL按联接表获取聚合细分

Question

MySQL数据库中有以下实体和关系：

• 每个Post有N个Review
• 每个Review具有1个Comment和一个状态(is_accepted(
• 每个Comment有1个User

所需结果：

我正在尝试获得一个特定帖子的评论汇总报告，按用户分组：

+---------+--------------+-----------------------+---------------------+
| user_id | review_count | review_accepted_count | review_denied_count |
+---------+--------------+-----------------------+---------------------+
| 1       | 3            | 2                     | 1                   |
| 2       | 5            | 1                     | 4                   |
| 3       | 1            | 1                     | 0                   |
+---------+--------------+-----------------------+---------------------+

我尝试过的：

SELECT
C.user_id,
COUNT(C.user_id) AS review_count,
(SELECT COUNT(*) FROM reviews WHERE `post_id` = R.post_id AND `user_id` = C.user_id AND `is_accepted` = 1) review_accepted_count,
(SELECT COUNT(*) FROM reviews WHERE `post_id` = R.post_id AND `user_id` = C.user_id AND `is_accepted` = 0) review_denied_count
FROM reviews R
INNER JOIN comments C ON C.id = R.comment_id
WHERE post_id = 1234
GROUP BY C.user_id

实际结果：

返回的review_accepted_count和review_denied_count列是所有评论的总数，而不是按用户分组

Answer 1

试试这个：

SELECT
C.user_id,
COUNT(C.user_id) AS review_count,
SUM(CASE WHEN `is_accepted` = 1 THEN 1 ELSE 0 END) AS review_accepted_count,
SUM(CASE WHEN `is_accepted` = 0 THEN 1 ELSE 0 END) AS review_denied_count
FROM reviews R
INNER JOIN comments C ON C.id = R.comment_id
WHERE post_id = 1234
GROUP BY C.user_id

在子查询review_acceptd_count和review_denied_count中，您应该通过review主键加入(在WHERE子句中(。您不需要进行子查询即可获得结果。这种方式更快。

如果列中只有1和0，则可以执行以下操作：

SUM(`is_accepted`) AS review_accepted_count