oracle——数据表的数据查询——oracle高级函数

sql查询

oracle高级函数

create table yuangong ( id number,
                        name varchar2(50),
                        zhiwei varchar2(50),
                        age number,
                        sex varchar2(20)
                      ) tablespace test
                      
insert into yuangong values(1,'周磊01','操作工',21,'男');
insert into yuangong values(2,'周磊02','操作工',21,'女');
insert into yuangong values(3,'周磊03','操作工',22,'男');
insert into yuangong values(4,'周磊04','操作工',24,'女');
insert into yuangong values(5,'周磊05','操作工',26,'男');
insert into yuangong values(6,'周磊06','操作工',24,'女');
insert into yuangong values(7,'周磊07','操作工',29,'男');
insert into yuangong values(8,'周磊08','操作工',22,'女');
insert into yuangong values(9,'周磊09','操作工',27,'男');
insert into yuangong values(10,'周磊10','操作工',25,'女');

insert into yuangong values(11,'周11磊','头目',34,'男');
insert into yuangong values(12,'周12磊','头目',31,'女');
insert into yuangong values(13,'周13磊','头目',31,'男');
insert into yuangong values(14,'周14磊','头目',34,'女');

insert into yuangong values(15,'周15磊','经理',45,'男');

select * from yuangong;

drop table yuangong;



1、rank()函数用于返回当前记录在窗口函数所指定的记录集中的排名

select name,rank() over( order by age ) position from yuangong;

rank()函数用于返回当前记录在窗口中的排序序号，对于排名相同的记录，函数返回排序相同的排序序号；

当出现多个排名相同的记录时，下一排名序号，将根据前一排名各个数进行跳跃。




2、相对于rank()函数，dense_rank()函数所获得的排名，则不会出现跳跃。

select name,dense_rank() over( order by age ) position from yuangong;

注意：rank()函数和dense_rank()函数对于排名相同的多条记录，返回相同的排名序列号，而函数row_number()函数每条记录都会存在唯一的排名序号

select name,row_number() over( order by age ) position from yuangong;



row_number()函数每次均返回唯一的排序序列号。

------------------------------------------------------------------------------------------------------------

分区窗口

对于窗口函数，利用partition by 关键字可以指定分区窗口

select xinzi.*,dense_rank() over ( partition by zhiwei order by jine ) position from xinzi,yuangong order by yuangong.id;