加入后 MySQL 限制 LEFT JOIN 子查询 [英] MySQL Limit LEFT JOIN Subquery after joining

问题描述

目前我有这个查询:

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 |评论ID1 |12 |空值3 |空值4 |25 |35 |45 |5

它有效，但它在加入之前限制了评论表.结果是，它选择了前 5 条评论并将其映射.id 为 5 的所有评论都会被忽略.

如何重写查询以选择最多 5 条评论的帖子?

当前表结构:

发帖:

<上一页>如果不存在 `post` 则创建表 (`id` int(11) NOT NULL AUTO_INCREMENT,`feed_id` int(11) 默认为空，`user_id` int(11) 默认为 NULL，`origin_id` int(11) 默认为 NULL，`content` longtext COLLATE utf8_unicode_ci NOT NULL,`启用` tinyint(1) NOT NULL,`created_at` 日期时间不为空，`updated_at` 日期时间不为空，主键(`id`)，KEY `IDX_5A8A6C8D51A5BC03` (`feed_id`),KEY `IDX_5A8A6C8DA76ED395` (`user_id`),KEY `IDX_5A8A6C8D56A273CC`(`origin_id`)) 引擎=InnoDB 默认字符集=utf8 排序=utf8_unicode_ci AUTO_INCREMENT=6 ；

评论:

<上一页>如果不存在 `comment` 则创建表 (`id` int(11) NOT NULL AUTO_INCREMENT,`feed_id` int(11) 默认为空，`user_id` int(11) 默认为 NULL，`post_id` int(11) 默认为 NULL，`content` longtext COLLATE utf8_unicode_ci NOT NULL,`启用` tinyint(1) NOT NULL,`created_at` 日期时间不为空，`updated_at` 日期时间不为空，主键(`id`)，KEY `IDX_9474526C51A5BC03` (`feed_id`),KEY `IDX_9474526CA76ED395` (`user_id`),KEY `IDX_9474526C4B89032C`(`post_id`)) 引擎=InnoDB 默认字符集=utf8 排序=utf8_unicode_ci AUTO_INCREMENT=11 ；

谢谢

解决方案

这将为每个帖子提供 5 条评论.

选择 p.*,C.*发件人左连接(选择一个.*FROM 评论在哪里(选择计数(*)来自评论 b其中 a.Post_ID = b.Post_ID 和a.ID <= b.ID) <= 5) c ON a.ID = c.Post_ID

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

这篇关于加入后 MySQL 限制 LEFT JOIN 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！