MySQL加入后限制LEFT JOIN子查询
目前我有这个查询：
SELECT post.id AS postID, sCom.id as CommentID FROM `post` LEFT JOIN (SELECT * FROM `comment` LIMIT 5) AS sCom ON sCom.post_id = post.id;
输出：
postID | CommentID
1 | 1
2 | null
3 | null
4 | 2
5 | 3
5 | 4
5 | 5
它的作品，但它限制JOINing之前的评论表。 结果是，它选择前5个评论并映射它。 对5个ID的所有评论都会被忽略。
我如何重写查询以选择最多5条评论的帖子？
目前的表结构：
发布：
CREATE TABLE IF NOT EXISTS `post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feed_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `origin_id` int(11) DEFAULT NULL, `content` longtext COLLATE utf8_unicode_ci NOT NULL, `enabled` tinyint(1) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `IDX_5A8A6C8D51A5BC03` (`feed_id`), KEY `IDX_5A8A6C8DA76ED395` (`user_id`), KEY `IDX_5A8A6C8D56A273CC` (`origin_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
评论：
CREATE TABLE IF NOT EXISTS `comment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feed_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `post_id` int(11) DEFAULT NULL, `content` longtext COLLATE utf8_unicode_ci NOT NULL, `enabled` tinyint(1) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), KEY `IDX_9474526C51A5BC03` (`feed_id`), KEY `IDX_9474526CA76ED395` (`user_id`), KEY `IDX_9474526C4B89032C` (`post_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;
谢谢
这会给你5条评论每个帖子。
SELECT p.*,
c.*
FROM Post p
LEFT JOIN
(
SELECT a.*
FROM Comments a
WHERE
(
SELECT COUNT(*)
FROM Comments b
WHERE a.Post_ID = b.Post_ID AND
a.ID <= b.ID
) <= 5
) c ON a.ID = c.Post_ID