Oracle开窗函数rank() over(partition by ... order by ... desc)

原文地址：https://www.cnblogs.com/LoveShare/p/16408656.html

1.创建表

 

-- Create table
create table TEST
(
  ID    NUMBER(10) not null,
  NAME  VARCHAR2(50),
  SCORE NUMBER(10)
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST
  add constraint PK_ID primary key (ID)
  using index ;

2.初始化数据

 

-- init data
insert into test (ID, NAME, SCORE)
values (3, 'test1', 98);

insert into test (ID, NAME, SCORE)
values (1, 'test1', 32);

insert into test (ID, NAME, SCORE)
values (2, 'test2', 78);

insert into test (ID, NAME, SCORE)
values (4, 'test4', 324);

3.普通select查看数据

 

--查询data
select * from test;

查询结果

4.使用rank() over(partition by ... order by ... desc)查询数据

 

select a.*, rank() over(partition by name order by score desc) as rn
  from test a

查询结果