oracle排名函数的使用方法分享
在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(partitionbybb.channel_nameorderbysum(aa.dk_serv_num)descnullslast)p1_rank1,
row_number()over(orderbysum(aa.dk_serv_num)descnullslast)rank1,
dense_rank()over(orderbynvl(sum(aa.dk_serv_num),0)desc)rank2,
rank()over(orderbysum(aa.dk_serv_num)descnullslast)rank3
例子：
procedureGetCompetitionRanking(p_UserIdininteger,p_CompetitionIdininteger,v_cursoroutCompetitionCursor)
is
v_startDatedate;
v_endDatedate;
tmp_startDatevarchar2(12);
tmp_endDatevarchar2(12);
tmp_datedate;
v_sql1varchar2(2000);
v_sql2varchar2(2000);
v_wherevarchar2(1000);
v_comTotalinteger;
v_groupTotalinteger;
v_comRankinginteger;
v_groupRankinginteger;
begin
selectt.start_date,t.end_dateintov_startDate,v_endDatefromtbl_competitiontwheret.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');
--grouppersonaltotal
selectcount(1)intov_groupTotalfromtbl_com_group_usera
wherea.com_group_idin
(
selectb.com_group_idfromtbl_com_group_userbwhereb.user_id=p_UserId
);
--Competitionpersonaltotal
selectcount(1)intov_comTotalfrom
(
selecta.com_group_idfromtbl_com_groupawherea.competition_id=p_CompetitionId
)ainnerjointbl_com_group_userbona.com_group_id=b.com_group_id;
--userincompetitionrankingandgroupranking
v_where:='t.DATA_TYPE_ID=1ANDt.STATUS=1AND
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)||')';
/*selectnofrom
(
selecta.USER_ID,dense_rank()over(orderbysum(nvl(DATA_NUMBER_2,0))desc)no
from
(
selectuser_idfromtbl_com_groupa
leftjointbl_com_group_userbona.com_group_id=b.com_group_id
wherea.competition_id=1
)aleftjoinVM_MASTER_DATAtona.user_id=t.user_idandt.DATA_TYPE_ID=1ANDt.STATUS=1AND
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')
groupbya.user_id
orderbynodesc
)whereuser_id=165*/
v_sql1:='selectnofrom
(
selecta.USER_ID,dense_rank()over(orderbysum(nvl(DATA_NUMBER_2,0))desc)no
from
(
selectuser_idfromtbl_com_groupa
leftjointbl_com_group_userbona.com_group_id=b.com_group_id
wherea.competition_id='||p_CompetitionId||'
)aleftjoinVM_MASTER_DATAtona.user_id=t.user_idand'||v_where||'
groupbya.user_id
orderbynodesc
)whereuser_id='||p_UserId;
dbms_output.put_line(v_sql1);
executeimmediatev_sql1intov_comRanking;
dbms_output.put_line('------------------------------');
--dbms_output.put_line(v_comRanking);
/*selectnofrom
(
selecta.USER_ID,dense_rank()over(orderbysum(nvl(DATA_NUMBER_2,0))desc)no
from
(
selecta.user_idfromtbl_com_group_usera
wherea.com_group_idin
(
selectb.com_group_idfromtbl_com_group_userbwhereb.user_id=165
)
)aleftjoinVM_MASTER_DATAtona.user_id=t.user_idandt.DATA_TYPE_ID=1ANDt.STATUS=1AND
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')
groupbya.user_id
orderbynodesc
)
whereuser_id=165*/
v_sql2:='selectnofrom
(
selecta.USER_ID,dense_rank()over(orderbysum(nvl(DATA_NUMBER_2,0))desc)no
from
(
selecta.user_idfromtbl_com_group_usera
wherea.com_group_idin
(
selectb.com_group_idfromtbl_com_group_userbwhereb.user_id='||p_UserId||'
)
)aleftjoinVM_MASTER_DATAtona.user_id=t.user_idand'||v_where||'
groupbya.user_id
orderbynodesc
)whereuser_id='||p_UserId;
dbms_output.put_line(v_sql2);
executeimmediatev_sql2intov_groupRanking;
--dbms_output.put_line('------------------------------');
--dbms_output.put_line(v_groupRanking);
ifv_comRankingisnullthen
v_comRanking:=v_comTotal;
endif;
ifv_groupRankingisnullthen
v_groupRanking:=v_groupTotal;
endif;
openv_cursorfor
selectv_comTotalCompetitionPersonalTotal,v_groupTotalUserInGroupPersonTotal,v_comRankingUserInCompRanking,v_groupRankingUserInGroupRankingfromdual;
exception
whenothersthen
null;
end;