Row_Number()： row_number()主要是为选出的每一条记录按照一定的排序方式生成一个行序号。 语法： ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression, ... [ n ] ] order_by_clause) 下面是学习row_number()的测试例子： CREATE TABLE #Test ( TypeNa
Row_Number()：
row_number()主要是为选出的每一条记录按照一定的排序方式生成一个行序号。
语法：
ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
下面是学习row_number()的测试例子：
CREATE TABLE #Test
(
TypeName VARCHAR(50),
TestName VARCHAR(50),
UpdateDate DATETIME
)
INSERT INTO #Test VALUES('Type1','Test1','2013-07-07')
INSERT INTO #Test VALUES('Type1','Test1','2013-07-06')
INSERT INTO #Test VALUES('Type1','Test1','2013-07-05')
INSERT INTO #Test VALUES('Type2','Test1','2013-07-04')
INSERT INTO #Test VALUES('Type2','Test1','2013-07-03')
INSERT INTO #Test VALUES('Type2','Test1','2013-07-02')
INSERT INTO #Test VALUES('Type2','Test1','2013-07-01')
1.按时间升序排序返回#Test表的行号：
SELECT ROW_NUMBER() OVER (ORDER BY UpdateDate) RowNumber,*
FROM #Test
结果集：
1
Type2
Test1
2013-07-01 00:00:00.000
2
Type2
Test1
2013-07-02 00:00:00.000
3
Type2
Test1
2013-07-03 00:00:00.000
4
Type2
Test1
2013-07-04 00:00:00.000
5
Type1
Test1
2013-07-05 00:00:00.000
6
Type1
Test1
2013-07-06 00:00:00.000
7
Type1
Test1
2013-07-07 00:00:00.000
2.以TypeName为分组 按时间排序:
SELECT ROW_NUMBER() OVER (PARTITION BY TypeName ORDER BY UpdateDate) RowNumber,*
FROM #Test
结果集：
1 Type1 Test1 2013-07-05 00:00:00.000
2 Type1 Test1 2013-07-06 00:00:00.000
3 Type1 Test1 2013-07-07 00:00:00.000
1 Type2 Test1 2013-07-01 00:00:00.000
2 Type2 Test1 2013-07-02 00:00:00.000
3 Type2 Test1 2013-07-03 00:00:00.000
4 Type2 Test1 2013-07-04 00:00:00.000
3.找出按时间排序第三条到第六条的数据：
;WITH TestOrder AS
(
SELECT ROW_NUMBER() OVER (ORDER BY UpdateDate) RowNumber,*FROM #Test
)
SELECT * FROM TestOrder WHERE RowNumber BETWEEN 3 AND 6
结果集：
3 Type2 Test1 2013-07-03 00:00:00.000
4 Type2 Test1 2013-07-04 00:00:00.000
5 Type1 Test1 2013-07-05 00:00:00.000
6 Type1 Test1 2013-07-06 00:00:00.000
MSDN 学习地址
，美国空间，香港虚拟主机，网站空间
