如何在普及SQL(9.1版)中进行分页？我需要做类似的事情：

//MySQL
SELECT foo FROM table LIMIT 10, 10

但是我找不到定义偏移量的方法。

解决方案

我也在MS Sql中遇到此问题...没有限制或者行号功能。我要做的是将最终查询结果的键(有时甚至是整个字段列表)插入具有标识列的临时表中……然后我从临时表中删除所需范围之外的所有内容……然后使用连接键和原始表，以带回我想要的项目。如果我们有一个不错的唯一键(如果没有的话)，这会起作用，好吧……这本身就是一个设计问题。

具有更好性能的替代方法是跳过删除步骤，而仅在最终联接中使用行号。另一个性能改进是使用TOP运算符，这样至少我们不必将所需的内容都拿走了。

所以...用伪代码...抓取物品80-89 ...

create table #keys (rownum int identity(1,1), key varchar(10))

insert #keys (key)
select TOP 89 key from myTable ORDER BY whatever

delete #keys where rownumber < 80

select <columns> from #keys join myTable on #keys.key = myTable.key

我们的页面调度要求我们能够传递当前页码和页面大小(以及一些其他过滤器参数)作为变量。由于select top @page_size在MS SQL中不起作用，因此我们想到了创建一个临时表或者变量表来为每行主键分配一个标识，该标识稍后可以根据所需的页码和大小进行过滤。

**请注意，如果我们具有GUID主键或者复合键，则只需将临时表上的对象ID更改为uniqueidentifier或者将其他键列添加到表中。

不利的一面是，它仍然必须将所有结果插入临时表中，但至少它只是键。这在MS SQL中有效，但应该能够以最小的调整就可以用于任何数据库。

declare @page_number int, @page_size
  int -- add any additional search
  parameters here

  
  --create the temporary table with the identity column and the id 

  --of the record that you'll be selecting.  This is an in memory

  --table, so if the number of rows you'll be inserting is greater

  --than 10,000, then you should use a temporary table in tempdb

  --instead.  To do this, use 

  --CREATE TABLE #temp_table (row_num int IDENTITY(1,1), objectid int)

  --and change all the references to @temp_table to #temp_table

  DECLARE @temp_table TABLE (row_num int
  IDENTITY(1,1), objectid int)
  
  --insert into the temporary table with the ids of the records

  --we want to return.  It's critical to make sure the order by

  --reflects the order of the records to return so that the row_num 

  --values are set in the correct order and we are selecting the
 
  --correct records based on the page
 INSERT INTO @temp_table
  (objectid)
  
  /* Example: Select that inserts
  records into the temporary table

  SELECT personid 
 FROM person WITH
  (NOLOCK) 
 inner join degree WITH
  (NOLOCK) on degree.personid =
  person.personid
 WHERE
  person.lastname = @last_name

  ORDER BY person.lastname asc,
  person.firsname asc

  */
  
  --get the total number of rows that we matched
 DECLARE @total_rows
  int
 SET @total_rows =
  @@ROWCOUNT

  --calculate the total number of pages based on the number of

  --rows that matched and the page size passed in as a parameter
 DECLARE
  @total_pages int 

  --add the @page_size - 1 to the total number of rows to 

  --calculate the total number of pages.  This is because sql 

  --alwasy rounds down for division of integers
 SET @total_pages =
  (@total_rows + @page_size - 1) /
  @page_size 
  
  --return the result set we are interested in by joining

  --back to the @temp_table and filtering by row_num
 /* Example:
  Selecting the data to return.  If the
  insert was done
  properly, then
  you should always be joining the table
  that contains
  the rows to return
  to the objectid column on the
  @temp_table
  
  SELECT person.*
 FROM person WITH
  (NOLOCK) INNER JOIN @temp_table
  tt
 ON person.personid =
  tt.objectid

  */

  --return only the rows in the page that we are interested in

  --and order by the row_num column of the @temp_table to make sure
 
  --we are selecting the correct records
 WHERE tt.row_num <
  (@page_size * @page_number) + 1 

  AND tt.row_num > (@page_size *
  @page_number) - @page_size
 ORDER
  BY tt.row_num

我最终在代码中进行了分页。我只是跳过循环中的第一条记录。

我以为我做了一种简单的分页方法，但是似乎普及的sql不允许子查询中使用order子句。但这应该适用于其他数据库(我在firebird上进行了测试)

select *
from (select top [rows] * from
(select top [rows * pagenumber] * from mytable order by id)
order by id desc)
order by id

在PSQL中测试过的查询：

select top n * 
from tablename 
where id not in(
select top k id
from tablename 
)

对于所有n =记录数，我们需要一次获取。
并且k = n的倍数(例如n = 5； k = 0,5,10,15，....)