如何从表中检索每条第 N 条记录? [英] How do I retrieve every Nth record from a table?
问题描述
查询保持不变,但偏移量不同.
The query remains constant, but the offset varies.
SELECT NAME from tbl WHERE alphabet_index='A' limit 880,1;
SELECT NAME from tbl WHERE alphabet_index='A' limit 1760,1;
SELECT NAME from tbl WHERE alphabet_index='A' limit 2640,1;
....
SELECT NAME from tbl WHERE alphabet_index='A' limit 34320,1;
有没有更好的方法来做到这一点,而不必运行具有不同偏移量的 N 个查询?
Is there a better way to do this without having to run N queries with different offsets?
更新:我正在尝试将名称范围构建为向下钻取的索引.因此,如果一个alphabet_index 有50000 条记录并且我想显示40 个范围,我正在计算offset = total/rangeCount
.我想要的最终结果是
Update: I am trying to build name ranges as an index for a drill down. So if an alphabet_index has 50000 records and I would like to display 40 ranges, I am calculating offset = total/rangeCount
. My desired end result would be
AA...-AA...
AA...-AB...
AB...-AC...
AC...-AC...
AD...-AF...
...
...
AW...-AZ...
推荐答案
您可以像这样在单个查询中执行不同的偏移量查询
You can do a varying offset query in a single query like this
select NAME from
(select @row:=@row+1 as row, t.NAME from
tbl t, (select @row := 0) y
where alphabet_index='A' order by alphabet_index) z
where row % 880 = 1;
这将通过@row 变量为每一行添加一个唯一的整数 id.然后它将通过模运算符和该变量每隔 880 选择一行.需要 order by 子句才能获得可重复的行为,否则结果实际上是随机的.
This will add a unique integer id to each row via the @row variable. Then it will select a row every other 880 via the modulo operator and that variable. An order by clause is required to get repeatable behavior, else the result would be effectively random.
这篇关于如何从表中检索每条第 N 条记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!