在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('2012-10-02','yyyy-mm-dd') AND t.DATA_DATE_1 = TO_DATE('2012-10-02','yyyy-mm-dd') AND t.DATA_DATE_1