1.首先介紹ROW_NUMBER() OVER的基本用法

2.看一下實(shí)例數(shù)據(jù)

初始化數(shù)據(jù)

create table employee (empid int ,deptid int ,salary decimal(10,2))

insert into employee values(1,10,5500.00)

insert into employee values(2,10,4500.00)

insert into employee values(3,20,1900.00)

insert into employee values(4,20,4800.00)

insert into employee values(5,40,6500.00)

insert into employee values(6,40,14500.00)

insert into employee values(7,40,44500.00)

insert into employee values(8,50,6500.00)

insert into employee values(9,50,7500.00)

數(shù)據(jù)結(jié)果顯示

根據(jù)部門(mén)分組（deptid），顯示每個(gè)部門(mén)的工資(salary)等級(jí)

這是想要得到的結(jié)果第二列根據(jù)部門(mén)進(jìn)行分組，第三列工資由高到低，rank進(jìn)行部門(mén)內(nèi)部的排列

3.簡(jiǎn)單分頁(yè)實(shí)現(xiàn)

SELECT  Row_Number() OVER (ORDER BY salary desc) rank,* FROM employee

根據(jù)上面1，2兩點(diǎn)我們可以看出這個(gè)SQL只是按照工資降序排序后，并沒(méi)有通過(guò)PARTITION BY COLUMN進(jìn)行分區(qū)（分組），然后通過(guò)row_number()從1開(kāi)始，為每一條分組記錄返回一個(gè)數(shù)字。結(jié)果如下

將上面SQL返回的結(jié)果集當(dāng)作一個(gè)數(shù)據(jù)表

（SELECT  Row_Number() OVER (ORDER BY salary desc) rank,* FROM employee）as NewTable

假如我們每頁(yè)5條記錄，

那么第一頁(yè)顯示select * from (SELECT  Row_Number() OVER (ORDER BY salary desc) rank,* FROM employee ) as NewTable where rank between 1 and 5

第二頁(yè)為select * from (SELECT  Row_Number() OVER (ORDER BY salary desc) rank,* FROM employee ) as NewTable where rank between 6 and 10

當(dāng)然我們第二頁(yè)這里只有4條記錄。

分頁(yè)就這樣實(shí)現(xiàn)了，對(duì)于多表查詢進(jìn)行分頁(yè)也是同樣的道理。

最新評(píng)論