ROW_NUMBER、DENSE_RANK和RANK函数

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。一般情况下row_number()函数和over连用
1）ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）
with
q1 as (select 1 a,1 b from dual
union select 1,2 from dual
union select 2,3 from dual
union select 2,4 from dual
union select 3,5 from dual
union select 3,6 from dual
union select 3,7 from dual
union select 3,8 from dual)
select a,b,row_number() over(partition by a order by b) oid
from q1;

–查询按照A分组后的第一个值
with
q1 as (select 1 a,1 b from dual
union select 1,2 from dual
union select 2,3 from dual
union select 2,4 from dual
union select 3,5 from dual
union select 3,6 from dual
union select 3,7 from dual