如何从n开始选择mySQL中的第n行 [英] How to select every nth row in mySQL starting at n

问题描述

我创建了一个问题此处我觉得有点肿.要略过这个问题，这是我的要求:

I have created a question Here That I think is a little bloated. To skim down this question here is my requirements:

我需要创建3个mySQL查询，以获取该行中的所有信息，以便可以将信息格式化为HTML表之一.

I need to create 3 mySQL queries that grab all information in that row so that I can format the information into one of HTML tables.

这是我选择的所有查询:

Here is the query that I have which selects everything:

$qry_questions = mysql_query("SELECT * FROM mbr_qa_questions ORDER BY q_votes DESC);
while($row = mysql_fetch_array($qry_questions){
    //Grab all information
}

现在，我需要进行三个查询，它们的行为与上面的查询相同，但是我需要执行以下操作:

Now, I need to make three queries that act like the one above, but I need it to do something like this:

pull everything from every third row in a table starting at row #1
pull everything from every third row in a table starting at row #2
pull everything from every third row in a table starting at row #3

然后，我将每个查询放入3列之一.

Then I would put each of those queries into one of the 3 columns.

我无法通过唯一ID来执行此操作.是的，它是一个自动递增的ID，但是可能必须将整行移动到另一个表中.

I can not do this by unique ID. Yes, it is an auto incrementing ID, but it will be probable that I might have to move whole rows into another table.

我已经添加了对每一行进行计数并将查询的结果放入正确的"bin"中的尝试

I've added my attempt to count each row and place the queried result into the right "bin"

//GIVE EACH ENTRY A COLUMN NUMBER
$count++;
if($count >= 4){
    $count = 1; 
}?> 

<div class="col" id="col-1">
    <?PHP
    while($count == 1){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>
<div class="col" id="col-2">
    <?PHP
    while($count == 2){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>
<div class="col" id="col-3">
    <?PHP
    while($count == 3){
        include("pieces/answers/newAnswerLayout.php"); 
    }
    ?>
</div>

推荐答案

这里是获取MySQL返回的结果集的一种方法. (但是，仅返回所有行并获取应用程序中的每三行可能会更容易).但这可以在MySQL中轻松完成.请注意，原始查询被包裹在别名为(c0>)的parens中(作为内联视图).

Here's one approach, to get the resultset returned by MySQL. (But it might be easier to just return all the rows, and get every third row within the app). But it can be done in MySQL pretty easily. Note that your original query is wrapped in parens (as an inline view) aliased as r.

SELECT r.*
  FROM ( 
         SELECT *
           FROM mbr_qa_questions
          ORDER BY q_votes DESC
       ) r
 CROSS
  JOIN ( SELECT @i := 0 ) s
HAVING ( @i := @i + 1) MOD 3 = 1

这将从第一行开始每隔三行返回一次.要获得从第二行和第三行开始的第三行，请将HAVING子句中的文字= 1分别替换为= 2或= 3.

That will return every third row, starting with the first row. To get every third row starting with the 2nd and 3rd row, replace the literal = 1 in the HAVING clause with = 2 or = 3 (respectively).

这篇关于如何从n开始选择mySQL中的第n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！