mysql - 从两个表中查找唯一 ID 并从两个表中查找相应的计数

Question

我正在运行一个mysql 数据库。我有两个表，分别存储对帖子的点赞和评论。

点赞表:

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id      | int(11)          | NO   |     | NULL    |                |
| user_id      | varchar(255)     | NO   |     | NULL    |                |
| created_at   | timestamp        | YES  |     | NULL    |                |
| updated_at   | timestamp        | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

评论表:

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id      | int(11)          | NO   |     | NULL    |                |
| user_id      | varchar(255)     | NO   |     | NULL    |                |
| comment      | varchar(255)     | NO   |     | NULL    |                |
| created_at   | timestamp        | YES  |     | NULL    |                |
| updated_at   | timestamp        | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

我需要获取唯一的用户 ID 以及他们的点赞数和评论数。

我运行了以下查询:

select * from
(select user_id, count(*) as like_count from post_likes group by user_id) as a,
(select user_id, count(*) as comment_count from post_comments group by user_id) as b
limit 5

查询 180 万条记录(两个表的总和)耗时 66 秒。此外，它没有给出正确的结果。

如果我执行 LEFT JOIN，它只返回左表中的匹配记录，而我需要从两个表中返回，即如果用户 ID 只喜欢但没有评论或只评论但不喜欢。

我想得到的是这样的:

+--------------+------------------+------+-----+---------+
| user_id      | like_count       | comment_count        |
+--------------+------------------+------+-----+---------+
| 1            | 1213             | 310                  |
| 2            | 1098             | 0                    |
| 3            | 0                | 115                  |
+--------------+------------------+------+-----+---------+

Answer 1

理想的方法是在用户表上LEFT JOIN，例如:

SELECT u.user_id, COUNT(l.id), COUNT(c.id)
FROM users u LEFT JOIN Likes l ON u.user_id = l.user_id
LEFT JOIN Comments c ON u.user_id = c.user_id;