ROW_NUMBER用法詳解

ROW_NUMBER用法詳解

文法格式：row_number() over(partition by 分組列 order by 排序列 desc)

分組列和排序列都可以多個字段組合

row_number() over()分組排序功能：

使用 row_number() over()函數時，over()裡頭的分組以及排序的執行晚于 where 、group by、 order by 的執行。

1. row_number() over()

使用時排序字段放在over(ORDER BY 字段)裡面，查詢記錄按排序字段升成序号升序輸出。

注意事項：用了over排序後,sql語句後面不用再寫Order BY子句排序。如果再寫Order by子句，記錄會按後面的Order by子句排序輸出，記錄排序不會按row_number産生的序号排序。

2. ROW_NUMBER() OVER PARTITION BY

使用 ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2 Asc) 語句，按OVER裡的字段1進行分組，然後按OVER裡的order BY 字段2 進行組内排序，每組都是從1開始。

這時可根據需要在後面寫另一個order BY 子句，記錄排序按這個子句排序輸出，跟ROW_NUMBER方法産生的序号無關。

示例

CREATE TABLE #temp_data(

id BIGINT PRIMARY KEY IDENTITY,

name NVARCHAR(50),

category INT,--分類id

sort_id INT,--排序id

addtime DATETIME

)

INSERT INTO #temp_data ( name,category,sort_id, addtime )VALUES ( \'水果11\', 1, 1,GETDATE());

INSERT INTO #temp_data ( name,category,sort_id, addtime )VALUES ( \'水果12\', 1, 2,GETDATE());

INSERT INTO #temp_data ( name,category,sort_id, addtime )VALUES ( \'水果23\', 2, 3,GETDATE());

INSERT INTO #temp_data ( name,category,sort_id, addtime )VALUES ( \'水果24\', 2, 4,GETDATE());

--SELECT * FROM #temp_data

--ROW_NUMBER() OVER

SELECT *,(ROW_NUMBER() OVER(ORDER BY category DESC,d.sort_id Asc)) as r_index

FROM #temp_data d

--ORDER BY id --如果再寫Order by子句，記錄會按後面的Order by子句排序

--ROW_NUMBER() OVER PARTITION BY 記錄按後面的order BY 子句排序輸出，跟ROW_NUMBER方法産生的序号無關。

SELECT (ROW_NUMBER() OVER(PARTITION BY d.category ORDER BY d.sort_id Asc)) as r_index,*

FROM #temp_data d ORDER BY category,r_index

DROP TABLE #temp_data