MySQL通过多个ID选择记录，每个ID限制为LIMIT [英] MySQL Selecting records by multiple IDs with LIMIT per each ID

问题描述

我对MySQLi查询感到困惑.场景是这样的: -我需要为每个帖子选择最多10条评论. -我正在使用此查询，但是它无法按我需要的方式工作 -帖子ID位于数组中

I'm stuck with my MySQLi query. The scenario is like this: - I need to select max 10 comments for each post. - I'm using this query but it doesn't work the way I need - Post IDs are in array

$comments_query = mysqli_query($con, "SELECT * FROM comments WHERE pid IN ({$decodedPostIds}) AND state='1' order by time LIMIT 10");

• LIMIT 10适用于整个评论.

在此先感谢您提供所有建议和答案. 附言我的英语不好意思. 彼得

Thanks in advance for all advices and answers. Ps. I'm sorry for my english. Peter

推荐答案

LIMIT 10

说结果将包含来自HOLE查询输出的10行.

says that the result will contain 10 lines from the HOLE query output.

比方说，在数据库中您有2条帖子:post1包含5条相关注释，而post2包含10条相关注释.

Lets say in database you have 2 posts: post1 contain 5 related comments, and post2 contain 10 related comments.

执行查询: SELECT * FROM comments WHERE pid IN ({$decodedPostIds}) AND state='1' order by time

将返回:

• post1:评论1
• post1:评论2
• post1:评论3
• post1:评论4
• post1:评论5
• post2:comment1
• post2:comment2
• post2:comment3
• post2:评论4
• post2:comment5
• post2:评论6
• post2:comment7
• post2:comment8
• post2:comment9
• post2:comment10
• post1: comment1
• post1: comment2
• post1: comment3
• post1: comment4
• post1: comment5
• post2: comment1
• post2: comment2
• post2: comment3
• post2: comment4
• post2: comment5
• post2: comment6
• post2: comment7
• post2: comment8
• post2: comment9
• post2: comment10

现在，在查询中添加LIMIT 10，将返回孔结果的前10行，即从 post1:comment1 到 post2:comment5

Now, adding LIMIT 10 to query, will return the FIRST 10 lines of the hole result, meaning from post1: comment1 to post2: comment5

您有2种解决方案:

1. 为每个帖子循环，并在该帖子上执行查询:

1. make a loop for each post and execute your query on that post:

选择*从注释中获取pid = $ post_id 且状态="1"(按时间限制10排列)

SELECT * FROM comments WHERE pid = $post_id AND state='1' order by time LIMIT 10

获取所有帖子，并使用PHP代码将每个帖子的前10条评论分组

fetch ALL posts and, using PHP code, group the first 10'th comments of each post

伪代码:

$rows = mysqli_query($con,'SELECT * FROM comments WHERE WHERE pid IN ({$decodedPostIds}) AND state='1' order by time LIMIT 10');

foreach($rows as $row){
    if(count($arr[$row['post_id']]) < 10){
        array_push($arr[$row['post_id']],$row)
    }
}

现在$ arr是一个数组，其中每个键都是post_id，并带有第10个注释作为值.

now $arr is array where each key is post_id with it 10'th comments as value.

IMO:我更喜欢解决方案2(讨厌循环执行查询).

IMO: I prefer solution 2(hate to execute queries in loop).

希望对您有所帮助.

这篇关于MySQL通过多个ID选择记录，每个ID限制为LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！