(1)分析函数:
rank ( ) over ( [query_partition_clause]order_by_clause )
dense_rank ( ) over([query_partition_clause] order_by_clause )
rownumber ( ) over ([query_partition_clause]order_by_clause )
可实现按指定的字段分组排序,对于相同分组字段的结果集进行排序,其中partition by 为分组字段,order by 指定排序字段
over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用
(2)Oracle分析函数与聚合函数的区别:
分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。(3)列子
求分组后排名情况可,例:
select* from
(
select a.*,row_number() over(PARTITION BY project_id order by task_Assign_time desc) asrId
fromrate_task_info a
)
where rId=1
(4)MySQL实现例子
SELECT * from(
select @rownum:=@rownum+1 rownum,a.*,
if(@objno=a.project_id or (@objno is null and a.project_id is null),
@rank:=@rank+1,
@rank:=1) as row_number,
@objno:=a.project_id
from(SELECT * from rate_task_info where current_node=700 order by project_id,task_Assign_timedesc)a,
(select @rownum :=0,@objno:=null,@rank:=0)b
)t
where t.row_number=1;