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

最新推荐文章于 2025-02-10 18:43:00 发布

转载最新推荐文章于 2025-02-10 18:43:00 发布 · 2.4k 阅读

标签

#oracle的left函数的用法

本文详细介绍了Oracle数据库中的四种排名函数：rank、dense_rank、row_number及partition。通过具体实例展示了这些函数的应用场景与区别，包括如何处理并列排名以及如何在特定分组内进行排名。

在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)

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;