伊图教程网[www.etoow.com]
http://www.etoow.com/html/2007-08/1186930906.html
10 Frank 38
11 John 40
可以观察到,是根据年龄升序排列了，并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,
如果不想按年龄排序,可以这样写
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],
FirstName,
Age
FROM Person
另外一个例子
SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下
Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M
注意,姓名M开始,序号又从1,2,3开始了
2 RANK函数
先看例子
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
FirstName,
Age
FROM Person
输出如下
Rank by Age FirstName Age
SQL 2005新增的几个函数之学习
'http://www.etoow.com/html/2007-08/1186930906.html