加入后MySQL Limit LEFT JOIN子查询 [英] MySQL Limit LEFT JOIN Subquery after joining
本文介绍了加入后MySQL Limit 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
它可以工作,但是在加入之前限制注释表.结果是,它选择了前5条注释并将其映射. ID超过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.
我该如何重写查询以选择最多包含5条评论的帖子?
How can i rewrite the query to have The post with maximum of 5 comments selected ?
当前表结构:
帖子:
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条评论.
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
这篇关于加入后MySQL Limit LEFT JOIN子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文