SQL 2005 新功能,方便排序
今天在园子里随便逛了一下,看到varmc写了一篇关于SQL分页方法的比较(原文),文中所述的第二种方法中有个“ROW_NUMBER() OVER(ORDER BY type,ID)”很奇怪,之前我没有看到过这种语法,正好机器上装有SQL 2005,就打开帮助,查询结果如下:
返回结果集分区内行的序列号,每个分区的第一行从 1 开始
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
-
<partition_by_clause>
-
将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
-
<order_by_clause>
-
确定将 ROW_NUMBER 值分配给分区中的行的顺序.
晕了,再看"Over"的帮助信息
确定在应用关联的窗口函数之前,行集的分区和排序。
适用范围:
排名窗口函数
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause> )
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
可以在单个查询中将多个排名或聚合窗口函数与单个 FROM 子句一起使用。但是,每个函数的 OVER 子句在分区和排序上可能不同。
-
PARTITION BY
-
将结果集分为多个分区。窗口函数分别应用于每个分区,并为每个分区重新启动计算。
-
value_expression
-
指定对相应 FROM 子句生成的行集进行分区所依的列。<value_expression> 只能引用 FROM 子句可用的列。不能引用选择列表中的表达式或别名。
-
<ORDER BY 子句>
-
指定应用排名窗口函数的顺序
原来SQL 2005有“排名函数”,ROW_NUMBER()是排名函数的一种,其它还有三种,可是PARTITION BY又是什么呢,不管了,先从简单的开始,写几条语句看看:
以Northwind举例
select ROW_NUMBER() over (order by CustomerID) as RowRank,* from Customers
返回以下类似数据表
RowRank CustomerID
1 ALFKI
2 ANATR
3 ANTON
4 AROUT
5 BERGS
。。。。。。
就是说,上述语句给Customers表增加了一列RowRank,形成了一个临时表,RowRank是按照CustomerID给表中的记录进行“排名”(排序)的,所以以字母"A"开头的排在前面,"B"开头的排在后面,就是order by CustomerID执行的结果。
如果加上PARTITION BY会是什么结果呢,看下面语句
select ROW_NUMBER() over ( PARTITION BY Country order by CustomerID) as RowRank,* from Customers
返回如下类似的表
RowRank CustomerID Country
1 CACTU Argentina
2 OCEAN Argentina
3 RANCH Argentina
1 ERNSH Austria
2 PICCO Austria
........
原来PARTITION BY类似于Group By,整个语句就是根据Country分组,再在分组内对CustomerID进行“排名”(排序),于是就有了上面的结果,如果Country列没有相同的值,则RowRank都是1。
ROW_NUMBER()很有用,尤其是对不是以Int类型为主键的数据进行排序时,以前看到的排序方法,非常复杂,现在好了,用ROW_NUMBER()就很简洁,速度快那是自然的啦
-