資源描述：
《SQL ROW_NUMBER() OVER函數(shù)的基本用法》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、語法：ROW_NUMBER()OVER(PARTITIONBYCOLUMNORDERBYCOLUMN)簡(jiǎn)單的說row_number()從1開始，為每一條分組記錄返回一個(gè)數(shù)字，這里的ROW_NUMBER()OVER(ORDERBYxlhDESC)是先把xlh列降序，再為降序以后的沒條xlh記錄返回一個(gè)序號(hào)。示例：xlhrow_num1700115002108537104row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2)表示根據(jù)COL1分組，在分組內(nèi)部根據(jù)COL2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)（組內(nèi)連續(xù)的唯一的
2、)實(shí)例：初始化數(shù)據(jù)createtableemployee(empidint,deptidint,salarydecimal(10,2))insertintoemployeevalues(1,10,5500.00)insertintoemployeevalues(2,10,4500.00)insertintoemployeevalues(3,20,1900.00)insertintoemployeevalues(4,20,4800.00)insertintoemployeevalues(5,40,6500.00)insertintoemployeevalues(6,4
3、0,14500.00)insertintoemployeevalues(7,40,44500.00)insertintoemployeevalues(8,50,6500.00)insertintoemployeevalues(9,50,7500.00)數(shù)據(jù)顯示為empiddeptidsalary-------------------------------------------------------------1105500.002104500.003201900.004204800.005406500.0064014500.0074044500.0085065
4、00.009507500.00需求：根據(jù)部門分組，顯示每個(gè)部門的工資等級(jí)預(yù)期結(jié)果：empiddeptidsalaryrank---------------------------------------------------------------------------------1105500.0012104500.0024204800.0013201900.00274044500.00164014500.0025406500.0039507500.0018506500.002SQL腳本：SELECT*,Row_Number()OVER(partitionby
5、deptidORDERBYsalarydesc)rankFROMemployee語法：ROW_NUMBER()OVER(PARTITIONBYCOLUMNORDERBYCOLUMN)簡(jiǎn)單的說row_number()從1開始，為每一條分組記錄返回一個(gè)數(shù)字，這里的ROW_NUMBER()OVER(ORDERBYxlhDESC)是先把xlh列降序，再為降序以后的沒條xlh記錄返回一個(gè)序號(hào)。示例：xlhrow_num1700115002108537104row_number()OVER(PARTITIONBYCOL1ORDERBYCOL2)表示根據(jù)COL1分組，在分組內(nèi)部根
6、據(jù)COL2排序，而此函數(shù)計(jì)算的值就表示每組內(nèi)部排序后的順序編號(hào)（組內(nèi)連續(xù)的唯一的)實(shí)例：初始化數(shù)據(jù)createtableemployee(empidint,deptidint,salarydecimal(10,2))insertintoemployeevalues(1,10,5500.00)insertintoemployeevalues(2,10,4500.00)insertintoemployeevalues(3,20,1900.00)insertintoemployeevalues(4,20,4800.00)insertintoemployeevalues(5
7、,40,6500.00)insertintoemployeevalues(6,40,14500.00)insertintoemployeevalues(7,40,44500.00)insertintoemployeevalues(8,50,6500.00)insertintoemployeevalues(9,50,7500.00)數(shù)據(jù)顯示為empiddeptidsalary-------------------------------------------------------------1105500.002104500.003201900.0042048
