从表中选择前N行 [英] select the TOP N rows from a table

问题描述

我正在进行一些分页，并且需要进行一些查询并获得定义为slicing的结果形式. 例如:我需要获取所有20n< x ＜ 40n等.

I am making some paging, and I need to make some query and get the result form defined slicing . for example: I need to get all "top" rows in range 20n < x < 40n etc.

SELECT * FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC;

现在我需要按名为ID的列进行滑动.

and now I need to make my sliding by column called ID .

有什么建议怎么做?我需要在mysql，mssql和oracle上运行查询.

Any suggestions how to so ? I need to run my query on mysql, mssql, and oracle.

推荐答案

假设您的页面大小为20条记录，并且您想获取第2页，则应按以下步骤操作:

Assuming your page size is 20 record, and you wanna get page number 2, here is how you would do it:

SQL Server，Oracle:

SQL Server, Oracle:

SELECT *   -- <-- pick any columns here from your table, if you wanna exclude the RowNumber
FROM (SELECT ROW_NUMBER OVER(ORDER BY ID DESC) RowNumber, * 
      FROM Reflow  
      WHERE ReflowProcessID = somenumber) t
WHERE RowNumber >= 20 AND RowNumber <= 40    

MySQL:

SELECT * 
FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC
LIMIT 20 OFFSET 20

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