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

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_Total integer;
v_groupTotal integer;
v_Ranking integer;
v_groupRanking integer;
begin
select t.start_date, t.end_date into v_startDate, v_endDate from tbl_petition t where t.petition_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__group_user a
where a._group_id in
(
select b._group_id from tbl__group_user b where b.user_id = p_UserId
);

-- Competition personal total
select count(1) into v_Total from
(
select a._group_id from tbl__group a where a.petition_id = p_CompetitionId
) a inner join tbl__group_user b on a._group_id = b._group_id;

--user in petition 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__group a
left join tbl__group_user b on a._group_id = b._group_id
where a.petition_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__group a
left join tbl__group_user b on a._group_id = b._group_id
where a.petition_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_Ranking;
dbms_output.put_line('------------------------------');
--dbms_output.put_line(v_Ranking);

/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__group_user a
where a._group_id in
(
select b._group_id from tbl__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__group_user a
where a._group_id in
(
select b._group_id from tbl__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_Ranking is null then
v_Ranking := v_Total;
end if;

if v_groupRanking is null then
v_groupRanking := v_groupTotal;
end if;

open v_cursor for
select v_Total CompetitionPersonalTotal, v_groupTotal UserInGroupPersonTotal, v_Ranking UserInCompRanking, v_groupRanking UserInGroupRanking from dual;

exception
when others then
null;
end;