使用Sql Server2005中新添加的row_number()和rank()返回行号或排名

row_number()和rank()都可以返回行号列

语法:
RANK ( )OVER ( [ < partition_by_clause > ] < order_by_clause > ) row_number ( )
OVER ( [ < partition_by_clause > ] < order_by_clause >

参数:
< partition_by_clause > 将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >确定将 RANK 值应用于分区中的行时所基于的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。

例子:

SELECT
cate_id,news_id,rank()over(orderbycate_iddesc)asrow_num
FROMnews

cate_id news_id row_num
-------- ----------- ------------
24 11482 1
24 11502 2
23 11503 3
23 11486 4
23 11487 5
22 11492 6
22 11493 7
22 11494 8
21 11495 9
21 11496 10

SELECT
cate_id,news_id,rank()over(orderbycate_iddesc)asrow_num
FROMnews

cate_id news_id row_num
-------- ----------- ------------
24 11482 1
24 11502 1
23 11503 3 前面两行所以为3
23 11486 3 orderby在cate_id上,cate_id=23的行号都为3
23 11487 3
22 11492 6
22 11493 6
22 11494 6
21 11495 9 前面9行所以为9
21 11496 9

row_number和rank的主要区别是rank返回的是唯一的行号,而rank返回的不一定是唯一值,它更具在orderby列上具有相同的值会返回一样的行号,某行的行号是相关行之前的行数加1。


set rowcount n
使 SQL Server 在返回指定的行数之后停止处理查询。(限制返回的行数)
注意:在 SQL Server 的下一个版本中，使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。在新的开发工作中，避免将 SET ROWCOUNT 语句与 DELETE、INSERT 和 UPDATE 语句一起使用，并计划修改当前使用该语句的应用程序。另外，对于当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句，建议您使用 TOP 语法重写它们。有关详细信息，请参阅 DELETE (Transact-SQL)、INSERT (Transact-SQL) 或 UPDATE (Transact-SQL)。

setrowcount5;
SELECT
cate_id,news_id,rank()over(orderbycate_iddesc)asrow_num
FROMnews

cate_id news_id row_num
-------- ----------- ------------
24 11482 1
24 11502 2
23 11503 3
23 11486 4
23 11487 5

要将此选项设置为 off 以便返回所有的行，请将 SET ROWCOUNT 指定为 0。

TOP 和 SET ROWCOUNT
限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。SET ROWCOUNT 与 TOP 的不同之处体现在下列方面：

SET ROWCOUNT 限制适用于计算 ORDER BY 后在结果集中生成行。如果指定了 ORDER BY，SELECT 语句将在从根据指定的 ORDER BY 分类进行排序的某个值集中选择 n 行后结束。

TOP 子句适用于指定了该子句的单个 SELECT 语句。SET ROWCOUNT 将一直有效，直到执行另一个 SET ROWCOUNT 语句，例如 SET ROWCOUNT 0 将关闭该选项。

重要事项：
使用 SET ROWCOUNT 不会影响 SQL Server 下一版本中的 DELETE、INSERT 和 UPDATE 语句。应避免在新的开发工作中将 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句一起使用，并计划修改当前使用它的应用程序。建议重写当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句以使用 TOP。

尽管 SET ROWCOUNT 对于 SELECT 语句的影响仍未改变，但将 TOP 与 SELECT 一起使用要优于使用 SET ROWCOUNT，原因如下：

当 SELECT、INSERT、UPDATE 和 DELETE 语句影响指定的行数时，SET ROWCOUNT 将导致这些语句中的大多数停止处理。此行为也适用于内部触发器的激发。

作为 SELECT 语句的一部分，查询优化器可以使用 TOP 子句中 expression 的值作为生成查询的执行计划的一部分。由于 SET ROWCOUNT 在执行查询的语句外使用，因此它的值不能用于生成查询的查询计划。