SQL之rank()、dense_rank()、row_number()的差异

  • A+
所属分类:Blog Oracle

rank() dense_rank() row_number()

with xx as
(select '1' testid, 'A1' testname, '20090520' creat_time
from dual
union all
select '2' testid, 'A2' testname, '20090521' creat_time
from dual
union all
select '2' testid, 'A3' testname, '20090522' creat_time
from dual
union all
select '2' testid, 'A5' testname, '20090522' creat_time
from dual
union all
select '3' testid, 'A4' testname, '20090523' creat_time
from dual)
select xx.*,
rank() over(order by xx.testid) rank,
dense_rank() over(order by xx.testid) dense_rank,
row_number() over(order by xx.testid) row_number
from xx order by testid;

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: