当前位置:Gxlcms > mysql > SqlServerRow_Number()学习

SqlServerRow_Number()学习

时间:2021-07-01 10:21:17 帮助过:8人阅读

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

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

人气教程排行