Oracle笔记--row_number rank dense_rank

create table TEST001
(
  EMPNO NUMBER(4),
  ENAME VARCHAR2(50),
  SAL   NUMBER(8,4)
)

INSERT INTO TEST001 values(1,'James',1000);
INSERT INTO TEST001 values(1,'Allen',800);
INSERT INTO TEST001 values(2,'Ak',600);
INSERT INTO TEST001 values(1,'UC',800);
INSERT INTO TEST001 values(2,'Blake',600);
INSERT INTO TEST001 values(2,'Ward',300);
INSERT INTO TEST001 values(3,'Turner',1200);

测试

1.全局

select t.*, 

row_number()over(order by sal) row_number,
rank()over(order by sal) rank,
dense_rank()over(order by sal) dense_rank

 from test001 t order by 3

row_number:按sal依次排序

rank :相同的值排名相同，然后跳跃增加

dense_rank：相同的值排名相同，然后递增

2.区内排序

select t.*, 
row_number()over(partition by empno order by sal) row_number,
rank()over(partition by empno order by sal) rank,
dense_rank()over(partition by empno order by sal) dense_rank
 from test001 t order by 1,3