在MS SQL Server 2005中限制样式功能

I've seen quite a few really horrid ways to do something like MySQL's LIMIT function for MS SQL.

我已经见过很多非常可怕的方法，比如使用MySQL的MS SQL极限函数。

Can anyone suggest a nice elegant way to do something like this:

有谁能提出一种优雅的方式来做这件事吗?

SELECT * FROM blah LIMIT 5,15;

but in MS SQL?

但在SQL女士吗?

Cheers!

干杯!

2 个解决方案

#1

SQL Server's equivalent to MySQL/PostgreSQL's LIMIT syntax is TOP (SQL Server 2000+), but TOP doesn't support the offset value...

SQL Server相当于MySQL/PostgreSQL的限制语法是TOP (SQL Server 2000+)，但是TOP不支持偏移值……

Assuming SQL Server 2005+, use:

假设SQL Server 2005+，使用:

SELECT x.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER (ORDER BY ?) AS rank
          FROM BLAH t) x
 WHERE x.rank BETWEEN 6 AND 20

Mind that you have to define a sort order for the ranking - replace the "?" with the appropriate column(s).

注意，您必须为排名定义排序顺序——用适当的列替换“?”。

#2

One of the ways to obtain the same in SQL Server for LIMIT 5,15 would be to use ROW_NUMBER() -

在SQL Server中获得限制为5、15的一种方法是使用ROW_NUMBER() -

With t As
(
    Select ...
        , ROW_NUMBER() OVER ( Order By ... ) As Num
    From Table
)
Select ...
From t
Where Num Between 5 And 15