SQL SERVER排名函数RANK,DENSE_RANK,NTILE,ROW_NUMBER
前言
本文意于用实例数据帮助理解SQL SERVER排名函数RANK,DENSE_RANK,NTILE,ROW_NUMBER。
准备工作
创建测试表:
create table test(
id int identity(1,1) primary key,
testid int,
name varchar(100)
)
插入测试数据:
insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 1,'LeeWhoee University'
insert into test(testid,name)select 2,'LeeWhoee University'
insert into test(testid,name)select 3,'排名函数'
insert into test(testid,name)select 4,'排名函数'
insert into test(testid,name)select 4,'排名函数'
用一个SQL语句来查看各个函数的使用:
select id,testid,
ROW_NUMBER() over( order by testid) as rownum,
RANK() over(order by testid) as ranknum,
DENSE_RANK() over(order by testid) as denseranknum,
Ntile(4) over ( order by testid) as ntilenum
from test
order by testid