SqlServerRow_Number()學(xué)習(xí)

Row_Number()： row_number()主要是為選出的每一條記錄按照一定的排序方式生成一個(gè)行序號(hào)。 語法： ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression, ... [ n ] ] order_by_clause) 下面是學(xué)習(xí)row_number()的測(cè)試?yán)樱?CREATE TABLE #Test ( TypeNa

Row_Number()：

row_number()主要是為選出的每一條記錄按照一定的排序方式生成一個(gè)行序號(hào)。

語法：

ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

下面是學(xué)習(xí)row_number()的測(cè)試?yán)樱?/p>

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.按時(shí)間升序排序返回#Test表的行號(hào)：

SELECT ROW_NUMBER() OVER (ORDER BY UpdateDate) RowNumber,*
FROM #Test

結(jié)果集：

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為分組 按時(shí)間排序:

SELECT ROW_NUMBER() OVER (PARTITION BY TypeName ORDER BY UpdateDate) RowNumber,*
FROM #Test

結(jié)果集：

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.找出按時(shí)間排序第三條到第六條的數(shù)據(jù)：

;WITH TestOrder AS
(
SELECT ROW_NUMBER() OVER (ORDER BY UpdateDate) RowNumber,*FROM #Test
)
SELECT * FROM TestOrder WHERE RowNumber BETWEEN 3 AND 6

結(jié)果集：

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 學(xué)習(xí)地址

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com