四大排序函数

迷南。 2022-09-28 01:46 123阅读 0赞

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.00
    union all
    select 2013,2,6577,20211,39420.00,1314,31536.00
    union all
    select 2013,2,6577,20220,60.00,2,111.00
    union all
    select 2013,2,6577,20457,60.00,2,48.00
    union all
    select 2013,2,6577,20458,60.00,2,48.00
    go
    select * from tb 
    /*
    2013	2	6577	20087	32640.00	1088	26112.00
    2013	2	6577	20211	39420.00	1314	31536.00
    2013	2	6577	20220	60.00	2	111.00
    2013	2	6577	20457	60.00	2	48.00
    2013	2	6577	20458	60.00	2	48.00
    */
    
    select ROW_NUMBER() over(partition by base_goods_id order by count)rn,* from tb 
    
    /*
    rn	year	week	base_goods_id	uid	amount	count	bean
    1	2013	2	6577	20220	60.00	2	111.00
    2	2013	2	6577	20457	60.00	2	48.00
    3	2013	2	6577	20458	60.00	2	48.00
    4	2013	2	6577	20087	32640.00	1088	26112.00
    5	2013	2	6577	20211	39420.00	1314	31536.00
    */
    
    select dense_rank()over(partition by base_goods_id order by count )rn,* from tb 
    /*
    rn	year	week	base_goods_id	uid	amount	count	bean
    1	2013	2	6577	20220	60.00	2	111.00
    1	2013	2	6577	20457	60.00	2	48.00
    1	2013	2	6577	20458	60.00	2	48.00
    2	2013	2	6577	20087	32640.00	1088	26112.00
    3	2013	2	6577	20211	39420.00	1314	31536.00
    */
    
    select rank()over(partition by base_goods_id order by count )rn,* from tb 
    
    /*
    1	2013	2	6577	20220	60.00	2	111.00
    1	2013	2	6577	20457	60.00	2	48.00
    1	2013	2	6577	20458	60.00	2	48.00
    4	2013	2	6577	20087	32640.00	1088	26112.00
    5	2013	2	6577	20211	39420.00	1314	31536.00
    */
    
    select ntile(2)over(partition by base_goods_id order by count )rn,* from tb 
    
    /*
    rn	year	week	base_goods_id	uid	amount	count	bean
    1	2013	2	6577	20220	60.00	2	111.00
    1	2013	2	6577	20457	60.00	2	48.00
    1	2013	2	6577	20458	60.00	2	48.00
    2	2013	2	6577	20087	32640.00	1088	26112.00
    2	2013	2	6577	20211	39420.00	1314	31536.00
    */