欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle排名函数的使用方法分享

程序员文章站 2023-11-18 21:56:58
在oracle中,有rank,dense_rank,row_number,以及分组排名partition。 说明: rank:排名会出现并列第n名,它之后的会跳过空出的...

在oracle中,有rank,dense_rank,row_number,以及分组排名partition。

说明:

rank:排名会出现并列第n名,它之后的会跳过空出的名次,例如:1,2,2,4
dense_rank:排名会出现并列第n名,它之后的名次为n+1,例如:1,2,2,3
row_number:排名采用唯一序号连续值,例如1,2,3,4
partition:将排名限制到某一分组

格式:
 

row_number() over(partition by bb.channel_name order by sum(aa.dk_serv_num) desc nulls last) p1_rank1,
row_number() over(order by sum(aa.dk_serv_num) desc nulls last) rank1,
dense_rank() over(order by nvl(sum(aa.dk_serv_num), 0) desc) rank2,
rank() over(order by sum(aa.dk_serv_num) desc nulls last) rank3

例子:

procedure getcompetitionranking(p_userid in integer, p_competitionid in integer, v_cursor out competitioncursor)
is
v_startdate date;
v_enddate date;
tmp_startdate varchar2(12);
tmp_enddate varchar2(12);
tmp_date date;
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
v_where varchar2(1000);

v_comtotal integer;
v_grouptotal integer;
v_comranking integer;
v_groupranking integer;
begin
select t.start_date, t.end_date into v_startdate, v_enddate from tbl_competition t where t.competition_id = p_competitionid;

tmp_date:= v_enddate+1;
tmp_startdate := to_char(v_startdate, 'yyyy-mm-dd');
tmp_enddate := to_char(tmp_date, 'yyyy-mm-dd');

--group personal total
select count(1) into v_grouptotal from tbl_com_group_user a
where a.com_group_id in
(
select b.com_group_id from tbl_com_group_user b where b.user_id = p_userid
);

-- competition personal total
select count(1) into v_comtotal from
(
select a.com_group_id from tbl_com_group a where a.competition_id = p_competitionid
) a inner join tbl_com_group_user b on a.com_group_id = b.com_group_id;

--user in competition ranking and group ranking
v_where := 't.data_type_id=1 and t.status=1 and
t.data_date_1 >= to_date('||chr(39)||tmp_startdate||chr(39)||','||chr(39)||'yyyy-mm-dd'||chr(39)||') and
t.data_date_1 < to_date('||chr(39)||tmp_enddate||chr(39)||','||chr(39)||'yyyy-mm-dd'||chr(39)||') ';

/*select no from
(
select a.user_id, dense_rank() over(order by sum(nvl(data_number_2, 0)) desc) no 
from
(
select user_id from tbl_com_group a
left join tbl_com_group_user b on a.com_group_id = b.com_group_id
where a.competition_id = 1
) a left join vm_master_data t on a.user_id = t.user_id and t.data_type_id=1 and t.status=1 and
t.data_date_1 >= to_date('2012-10-02','yyyy-mm-dd') and
t.data_date_1 < to_date('2012-12-01','yyyy-mm-dd') 
group by a.user_id
order by no desc
) where user_id = 165*/

v_sql1 := 'select no from
(
select a.user_id, dense_rank() over(order by sum(nvl(data_number_2, 0)) desc) no 
from
(
select user_id from tbl_com_group a
left join tbl_com_group_user b on a.com_group_id = b.com_group_id
where a.competition_id = '||p_competitionid||'
) a left join vm_master_data t on a.user_id = t.user_id and '|| v_where||'
group by a.user_id
order by no desc 
) where user_id = '||p_userid;

dbms_output.put_line(v_sql1);
execute immediate v_sql1 into v_comranking;
dbms_output.put_line('------------------------------');
--dbms_output.put_line(v_comranking);

/*select no from
( 
select a.user_id, dense_rank() over(order by sum(nvl(data_number_2, 0)) desc) no 
from
(
select a.user_id from tbl_com_group_user a
where a.com_group_id in
(
select b.com_group_id from tbl_com_group_user b where b.user_id = 165
)
) a left join vm_master_data t on a.user_id = t.user_id and t.data_type_id=1 and t.status=1 and
t.data_date_1 >= to_date('2012-10-02','yyyy-mm-dd') and
t.data_date_1 < to_date('2012-12-01','yyyy-mm-dd') 
group by a.user_id
order by no desc
)
where user_id=165*/

v_sql2 := 'select no from
(
select a.user_id, dense_rank() over(order by sum(nvl(data_number_2, 0)) desc) no
from
(
select a.user_id from tbl_com_group_user a
where a.com_group_id in
(
select b.com_group_id from tbl_com_group_user b where b.user_id = '||p_userid||'
)
) a left join vm_master_data t on a.user_id = t.user_id and '|| v_where||'
group by a.user_id
order by no desc 
) where user_id = '||p_userid;

dbms_output.put_line(v_sql2);
execute immediate v_sql2 into v_groupranking;
--dbms_output.put_line('------------------------------');
--dbms_output.put_line(v_groupranking);

if v_comranking is null then
v_comranking := v_comtotal;
end if;

if v_groupranking is null then
v_groupranking := v_grouptotal;
end if;

open v_cursor for
select v_comtotal competitionpersonaltotal, v_grouptotal useringrouppersontotal, v_comranking userincompranking, v_groupranking useringroupranking from dual;

exception
when others then
null;
end;