如何在SQL Server中创建MySQL的LIMIT？ [重复]

This question already has an answer here:

这个问题在这里已有答案：

Paging SQL Server 2005 Results 6 answers
分页SQL Server 2005结果6个答案

I'm retrieving thousands data from database. I would like to show on web pages by limit of 100 records. I can show First 100 record with the following SQL:

我正在从数据库中检索数千个数据。我想通过限制100条记录在网页上显示。我可以使用以下SQL显示前100条记录：

SELECT TOP 100 * FROM TBLStock

And How can I navigate next records 101 to 200, 201 to 300, etc.. ? Please Help me. I am using SQL Server 2005. HELP! How can I write in SQL Server like LIMIT in MySQL?

如何导航下一个记录101到200,201到300等。？请帮帮我。我正在使用SQL Server 2005.帮助！如何在MySQL中编写像LIMIT这样的SQL Server？

2 个解决方案

#1

Method 1: SELECT TOP 100 FROM (SELECT TOP 500 ORDER BY )

方法1：选择TOP 100 FROM（选择TOP 500 ORDER BY）

AFAIK this is the only way before Yukon (MS SQL server 2005). Double select with reverse ordering in the middle.

AFAIK这是Yukon之前的唯一途径（MS SQL server 2005）。双重选择，中间有反向排序。

Surprisingly, it is also said to be efficient.

令人惊讶的是，它也被认为是有效的。

Method 2: Use Row_Number() function that is available starting SQL Server 2005, as the other post suggests.

方法2：使用从SQL Server 2005开始可用的Row_Number（）函数，正如另一篇文章所建议的那样。

#2

SELECT t.*
FROM
(
    SELECT first_column, second_column, third_column, etc,
        ROW_NUMBER() OVER (ORDER BY sort_column) AS row_num
    FROM your_table
) AS t
WHERE t.row_num BETWEEN 50 AND 100
ORDER BY t.row_num

#1