rank() partition by 排名次

rank()排名

partition by分组与group by相比各有优势，在这里就省略100字。。。。

以下为案例：

create table student -- 学生表
(
sid integer primary key,-- ID
sname nvarchar2(100),-- 姓名
sex nvarchar2(50),-- 性别
age integer,-- 年龄
address nvarchar2(200) -- 住址
);
insert into student values(8001,'张三','男',20,'湖北武汉');
insert into student values(8002,'李四','女',21,'湖北襄阳');
insert into student values(8003,'王五','女',21,'湖北襄阳');
insert into student values(8004,'赵六','男',22,'湖北襄阳');
insert into student values(8005,'孙琦','女',19,'湖北襄阳');
insert into student values(8006,'克鲁斯','女',19,'湖北襄阳');
insert into student values(8007,'丝路币','男',19,'湖北襄阳');
insert into student values(8008,'史酷比','男',23,'湖北襄阳');
insert into student values(8009,'库哈斯','男',20,'湖北襄阳');
insert into student values(8010,'罗欧克','女',19,'湖北襄阳');

create table workinfo
(
wid integer primary key,
sid integer ,
CONSTRAINT sid foreign key(sid) references student(sid),
city nvarchar2(100),-- 就业城市
wage number(10,2),-- 月薪
workdate date,-- 就业时间
eid integer ,
CONSTRAINT eid foreign key(eid) references Emp(eid)-- 介绍人
);

insert into workinfo values(1001,8001,'上海',5000,to_date('2009-3-5','yyyy-MM-dd'),6005);
insert into workinfo values(1002,8002,'上海',4500,to_date('2010-3-5','yyyy-MM-dd'),6005);
insert into workinfo values(1003,8005,'上海',5500,to_date('2009-3-5','yyyy-MM-dd'),6005);
insert into workinfo values(1004,8003,'杭州',4000,to_date('2009-12-5','yyyy-MM-dd'),6006);
insert into workinfo values(1005,8004,'杭州',4500,to_date('2007-12-5','yyyy-MM-dd'),6006);
insert into workinfo values(1006,8006,'南京',4000,to_date('2009-12-5','yyyy-MM-dd'),6006);
insert into workinfo values(1007,8008,'杭州',4000,to_date('2009-12-5','yyyy-MM-dd'),6006);
insert into workinfo values(1008,8010,'上海',4000,to_date('2007-12-5','yyyy-MM-dd'),6007);
insert into workinfo values(1009,8007,'杭州',4400,to_date('2008-12-5','yyyy-MM-dd'),6007);
insert into workinfo values(1010,8009,'杭州',4000,to_date('2009-12-5','yyyy-MM-dd'),6007);

 

select RANK() OVER(PARTITION BY city ORDER BY wage DESC) 名次,city ,wage,s.sid,sname,sex
from student s,workinfo w
where w.sid=s.sid

 

 

效果如下图：