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_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 ;