如何将表中的第n行，或者如何将一个表的子集分解成相同大小的集合或行?

I have a table of heterogeneous pieces of data identified by a primary key (ID) and a type identifier (TYPE_ID). I would like to be able to perform a query that returns me a set of ranges for a given type broken into even page sizes. For instance, if there are 10,000 records of type '1' and I specify a page size of 1000, I want 10 pairs of numbers back representing values I can use in a BETWEEN clause in subsequent queries to query the DB 1000 records at a time.

我有一个由主键(ID)和类型标识符(TYPE_ID)标识的异构数据表。我希望能够执行一个查询，它返回给我一组给定类型的范围，甚至是页面大小。例如，如果有10,000个类型“1”的记录，并且我指定了一个页面大小为1000，那么我想要10对返回值，以表示我可以在后续查询中使用的中间子句中的值，以便一次查询DB 1000记录。

My initial attempt was something like this

我最初的尝试是这样的

select id, rownum from CONTENT_TABLE 
where type_id = ? and mod(rownum, ?) = 0

But this doesn't work.

但这是行不通的。

3 个解决方案

#1

rownum is "evaluated" when the selected records are returned, after evaluating the where clause of a select query. Therefore, you need to select on rownum in an another query. I give r as an alias for rownum:

在计算select查询的where子句之后，当返回所选记录时，将对rownum进行“评估”。因此，您需要在另一个查询中选择rownum。我给r作为rownum的别名:

select id from (
  select 
    id, 
    rownum r
  from 
    CONTENT_TABLE 
   where type_id = ? 
)
where mod(r, ?) = 0

#2

Have you looked at NTILE? http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014248

你看过NTILE了吗?http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm i1014248

#3

-1

Hi Use below SQL to find the every nth row in a table:

Hi使用下面的SQL查找表中的第n行:

SQL Query:

SQL查询:

SELECT * 
FROM table_name
WHERE (ROWID,0) in (SELECT ROWID, MOD(ROWNUM, n)
FROM table_name);
Replace n with 2, 3, 4 , 5 ...

#1