Oracle¶ÔÓÚrankºÍdense_rankÒѾ֧³ÖºÏ¼Æ¹¦ÄÜ£¬²»¹ýÕâ´ÎÎÒ½ö½öʹÓÃÁËÆä·ÖÎö¹¦ÄÜ¡£¾ßÌåÓï·¨ÈçÏ£ºRANK ( ) OVER ( [query_partition_clause] order_by_clause )¡£
ÏÂÃæ¸ø³öһЩÀ´×ÔÍøÉϵÄʾÀý£º
TABLE£ºS £¨subject£¬mark£©
Êýѧ£¬80
ÓïÎÄ£¬70
Êýѧ£¬90
Êýѧ£¬60
Êýѧ£¬100
ÓïÎÄ£¬88
ÓïÎÄ£¬65
ÓïÎÄ£¬77
ÏÖÔÚÎÒÏëÒªµÄ½á¹ûÊÇ£ºÃ¿ÃÅ¿ÆÄ¿µÄÇ°3ÃûµÄ·ÖÊý
Êýѧ£¬100
Êýѧ£¬90
Êýѧ£¬80
ÓïÎÄ£¬88
ÓïÎÄ£¬77
ÓïÎÄ£¬70
ÄÇôÓï¾ä¾ÍÕâôд£º
select * from (select rank() over(partition by subject order by mark desc) rk,S.* from S) T
where T.rk<=3;
dense_rankÓërank()Ó÷¨Ï൱£¬µ«ÊÇÓÐÒ»¸öÇø±ð£ºdence_rankÔÚ´¦ÀíÏàͬµÄµÈ¼¶Ê±£¬µÈ¼¶µÄÊýÖµ²»»áÌø¹ý¡£rankÔòÌø¹ý¡£
ÀýÈ磺±í
A B C
a liu wang
a jin shu
a cai kai
b yang du
b lin ying
b yao cai
b yang 99
ÀýÈ磺µ±rankʱΪ£º
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 4
¶øÈç¹ûÓÃdense_rankʱΪ£º
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A B C LIU
a cai kai 1
a jin shu 2
a liu wang 3
b lin ying 1
b yang du 2
b yang 99 2
b yao cai 3