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

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;