欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Sql Server Row_Number() 学习

程序员文章站 2024-01-27 19:58:04
...

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 学习地址

,美国空间,香港虚拟主机,网站空间