四大排序函数

 row_number() over(partition by clause order by filed asc|desc )----连续的从1开始到n的排序号

 rank() over ( partition by clause order by filed asc|desc)---不连续的可以出现重复排序号 

dense_rank() over (partition by clause order by filed asc|desc)--连续的可以出现重复排序号 

ntile (integer_expression) over (partition by clause order by  filed asc|desc )--连续的可以分组排序并且排序号是连续的

----案例create table tb ([year] [int] NOT NULL,[week] [int] NOT NULL,[base_goods_id] [int] NOT NULL,[uid] [bigint] NOT NULL,[amount] [money] NOT NULL,[count] [int] NOT NULL,[bean] [money] NOT NULL)insert tb  select 2013,2,6577,20087,32640.00,1088,26112.00union allselect 2013,2,6577,20211,39420.00,1314,31536.00union allselect 2013,2,6577,20220,60.00,2,111.00union allselect 2013,2,6577,20457,60.00,2,48.00union allselect 2013,2,6577,20458,60.00,2,48.00goselect * from tb /*2013265772008732640.00108826112.002013265772021139420.00131431536.002013265772022060.002111.002013265772045760.00248.002013265772045860.00248.00*/select ROW_NUMBER() over(partition by base_goods_id order by count)rn,* from tb /*rnyearweekbase_goods_iduidamountcountbean12013265772022060.002111.0022013265772045760.00248.0032013265772045860.00248.0042013265772008732640.00108826112.0052013265772021139420.00131431536.00*/select dense_rank()over(partition by base_goods_id order by count )rn,* from tb /*rnyearweekbase_goods_iduidamountcountbean12013265772022060.002111.0012013265772045760.00248.0012013265772045860.00248.0022013265772008732640.00108826112.0032013265772021139420.00131431536.00*/select rank()over(partition by base_goods_id order by count )rn,* from tb /*12013265772022060.002111.0012013265772045760.00248.0012013265772045860.00248.0042013265772008732640.00108826112.0052013265772021139420.00131431536.00*/select ntile(2)over(partition by base_goods_id order by count )rn,* from tb /*rnyearweekbase_goods_iduidamountcountbean12013265772022060.002111.0012013265772045760.00248.0012013265772045860.00248.0022013265772008732640.00108826112.0022013265772021139420.00131431536.00*/