mysql left join子查询条数_MySQL加入后限制LEFT JOIN子查询

currently i have this Query:

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;

Output:

postID | CommentID

1 | 1

2 | null

3 | null

4 | 2

5 | 3

5 | 4

5 | 5

It works but it LIMITs the comment Table before JOINing. The result is, that it selects the first 5 comments and maps it. All comments over an id of 5 gets ignored.

How can i rewrite the query to have The post with maximum of 5 comments selected ?

The current table structure:

Post :

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 ;

Comment:

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 ;

Thanks

解决方案

This will give you 5 comments for every post.

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