1 --Rank()返回数据项的排名,排名相同时会出现名次跳跃的情况 2 --Dense_Rank()返回数据项的排名,排名相同不会出现跳跃的情况 3 select a.prd_type_id, 4        sum(a.amount), 5        rank() over(order by sum(a.amount) desc) /*rank函数*/, 6        dense_rank() over(order by sum(a.amount) desc) /*dense_rank函数*/ 7   from all_sales a 8  where a.year = 2003 9    and a.amount is not null10  group by a.prd_type_id11  order by a.prd_type_id12  13 --使用nulls firsr 和nulls last控制空值的排名14 select a.prd_type_id,15        sum(a.amount),16        rank() over(order by sum(a.amount) desc nulls last),17        dense_rank() over(order by sum(a.amount) asc nulls first)18   from all_sales a19  where a.year = 200320  group by a.prd_type_id21  order by a.prd_type_id22  23  --分析函数与partition by子句联合使用进行分组24  select b.prd_type_id,25         b.month,26         sum(b.amount),27         rank() over(partition by c.month order by sum(b.amount) desc nulls last)28    from all_sales b29   where b.year = 200330     and b.amount is not null31   group by b.prd_type_id, b.month32   order by b.prd_type_id, b.month33   34 --分析函数与ROLLUP CUBE GROUPING SETS等操作符的联合使用35 --实例136 select c.prd_type_id,37        sum(c.amount),38        rank() over(order by sum(c.amount) desc nulls last) as rk39   from all_sales c40  where c.year = 200341  group by rollup(c.prd_type_id)42  order by c.prd_type_id43 --实例244 select d.prd_type_id,45        d.emp_id,46        sum(d.amount),47        rank() over(order by sum(d.amount) desc) as rk48   from all_sales d49  where d.year = 200350  group by cube(d.prd_type_id, d.emp_id)51  order by d.prd_type_id, d.emp_id52  53  --实例3：仅返回小计信息54 select d.prd_type_id,55        d.emp_id,56        sum(d.amount),57        rank() over(order by sum(d.amount) desc) as rk58   from all_sales d59  where d.year = 200360  group by grouping sets(d.prd_type_id, d.emp_id)61  order by d.prd_type_id, d.emp_id