Rank()Over(Partiton by … Order By …Nulls First/Last)
Dense_rank ()Over(Partiton by … Order By …Nulls First/Last)
Row_number ()Over(Partiton by … Order By …Nulls First/Last)
Lag(…,…)Over(Partiton by … Order By …Nulls First/Last)
Lead(…,…)Over(Partiton by … Order By …Nulls First/Last)
Aggregate_function(…)Over(Partition by … Order By …Nulls First/Last)
Aggregate_function(…) Keep(Dense_rank First/Last Order By …Nulls First/Last) Over (Partition by …)
[@more@]功能测试用表: v_test(id int,group_id varchar2(10),name varchar2(10),num int)
ID | GROUP_ID | NAME | NUM |
1 | 001 | ok | 13 |
2 | 001 | yes | 25 |
3 | 001 | no | 25 |
4 | 001 | hi | 25 |
5 | 001 | no | 17 |
6 | 001 | no | 34 |
7 | 002 | no | 26 |
8 | 002 | oh | 19 |
一、Rank()Over(Partiton by … Order By …Nulls First/Last)
Dense_rank ()Over(Partiton by … Order By …Nulls First/Last)
Row_number ()Over(Partiton by … Order By …Nulls First/Last)
SQL语句:
-------------------------------------------------------------------------------------------------------
select id,group_id,name,num,
rank()over(partition by group_id order by name asc nulls last) rk,
dense_rank()over(partition by group_id order by name asc nulls first) dr,
row_number()over(partition by group_id order by name asc nulls last) rnfrom v_test;
-------------------------------------------------------------------------------------------------------
结果:
ID | GROUP_ID | NAME | NUM | RK | DR | RN |
4 | 001 | hi | 25 | 1 | 1 | 1 |
3 | 001 | no | 25 | 2 | 2 | 2 |
5 | 001 | no | 17 | 2 | 2 | 3 |
6 | 001 | no | 34 | 2 | 2 | 4 |
1 | 001 | ok | 13 | 5 | 3 | 5 |
2 | 001 | yes | 25 | 6 | 4 | 6 |
7 | 002 | no | 26 | 1 | 1 | 1 |
8 | 002 | oh | 19 | 2 | 2 | 2 |
二、Lag(…,…)Over(Partiton by … Order By …Nulls First/Last)
Lead(…,…)Over(Partiton by … Order By …Nulls First/Last)
SQL语句:
-------------------------------------------------------------------------------------------------------
select id,group_id,name,num,
lag(name,1)over(partition by group_id order by name asc nulls last) lg,
lead(name,1)over(partition by group_id order by name asc nulls first) ld
from v_test;
-------------------------------------------------------------------------------------------------------
结果:
ID | GROUP_ID | NAME | NUM | LG | LD |
4 | 001 | hi | 25 | no | |
3 | 001 | no | 25 | hi | no |
5 | 001 | no | 17 | no | no |
6 | 001 | no | 34 | no | ok |
1 | 001 | ok | 13 | no | yes |
2 | 001 | yes | 25 | ok | |
7 | 002 | no | 26 | oh | |
8 | 002 | 来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7988176/viewspace-919052/,如需转载,请注明出处,否则将追究法律责任。
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
|
转载于:http://blog.itpub.net/7988176/viewspace-919052/