mysql - 如何将表连接到自身，对连接的字段进行分组？

Question

我有一个表，每次创建帖子时都会创建一条记录，而另一个表会在每次帖子被收藏时创建一条记录。

CREATE TABLE `posts` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`post_id`)
)

CREATE TABLE `favorited_posts` (
  `user_id` bigint(20) NOT NULL,
  `post_id` bigint(20) NOT NULL,
  KEY `user_id` (`user_id`),
  KEY `post_id` (`post_id`),
  FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`)
)

下面的查询返回一个结果集，显示一个帖子被收藏了多少次

SELECT p.post_id, p.title,
    COUNT(fp.post_id) as num_favorites
FROM posts p
LEFT JOIN favorited_posts fp
ON fp.post_id = p.post_id
WHERE p.post_id BETWEEN 0 AND 10000
GROUP BY 1;

+-------------------+----------------+
| post_id  | title  |  num_favorites |
+-------------------+----------------+
| 1        | abc    |  15            |
| 2        | hello  |  0             |
| 3        | test   |  7             |
+----------+--------+----------------+

如何更改查询以创建一个具有 true/false、0/1 值的临时列来表示用户是否收藏了特定帖子？它会产生这样的结果集(用户收藏了帖子 2 和 3)

+-------------------+----------------+---------------+
| post_id  | title  |  num_favorites | has_favorited |
+-------------------+----------------+---------------+
| 1        | abc    |  15            | 0             |
| 2        | hello  |  0             | 1             |
| 3        | test   |  7             | 1             |
+----------+--------+----------------+---------------+

我可以通过查询检索特定用户收藏的帖子，但不知道如何将此信息作为捕获 post_id、标题和收藏夹总数的单个查询的一部分进行检索。

SELECT * FROM favorited_posts
WHERE user_id = 10;

Answer 1

只需在左连接中使用第二个查询:

SELECT p.post_id, p.title, IF(COUNT(fp.post_id)>0, 'True', 'False') as user_favorite
FROM posts p
LEFT JOIN (
        SELECT post_id FROM favorited_posts
        WHERE user_id = 10
    ) fp ON fp.post_id = p.post_id
WHERE p.post_id BETWEEN 0 AND 10000
GROUP BY 1;

此外，您在 favorited_posts 中遗漏了 user 表的外键。