您的位置:澳门新葡8455最新网站 > 数据库管理 > 八个排名函数,Server排名函数

八个排名函数,Server排名函数

发布时间:2019-10-11 21:46编辑:数据库管理浏览(154)

    近来在MySQL中相见分组排序查询时,猛然开掘MySQL中从未row_number() over(partition by colname)那样的分组排序。
    况兼鉴于MySQL中从不临近于SQL Server中的row_number()、rank()、dense_rank()等排名函数,全部找到以下完毕方式,在那轻松记录一下。

    正文为原创,如需转发,请评释小编和出处,多谢!
    上一篇:SQL Server二〇〇七随笔(2):公用表表明式(CTE)的递归调用

     

        排行函数是SQL Server2006新加的效应。在SQL Server二〇〇七中有如下八个排行函数:

    第一创立二个表并插入测量检验数据。

    1. row_number

    create table demo.Student (
       ID int(11) NOT NULL AUTO_INCREMENT,
       StuNo varchar(32) NOT NULL,
       StuName varchar(10) NOT NULL,
       StuAge int(11) DEFAULT NULL,
       PRIMARY KEY (ID)
     )
     engine=innodb auto_increment=1 default charset=utf8 collate=utf8_general_ci;
    
    insert into demo.Student(StuNo,StuName,StuAge) values('A001','小明',22);
    insert into demo.Student(StuNo,StuName,StuAge) values('A005','小李',23);
    insert into demo.Student(StuNo,StuName,StuAge) values('A007','小红',24);
    insert into demo.Student(StuNo,StuName,StuAge) values('A003','小明',22);
    insert into demo.Student(StuNo,StuName,StuAge) values('A002','小李',23);
    insert into demo.Student(StuNo,StuName,StuAge) values('A004','小红',24);
    insert into demo.Student(StuNo,StuName,StuAge) values('A006','小王',25);
    
    select * from demo.Student;
    

    2. rank

    测量检验数据如下:

    3. dense_rank

    图片 1

    4. ntile   
        上边分别介绍一下那八个排行函数的成效及用法。在介绍从前假使有一个t_table表,表结构与表中的数码如图1所示:

     

    图片 2

    实现row_number()排行函数,按学号(StuNo)排序。

    图1

    -- @row_number:=0,设置变量@row_number的初始值为0。
    -- @row_number:=@row_number+1,累加@row_number的值。
    select ID,StuNo,StuName,StuAge,@row_number:=@row_number+1 as row_number 
    from demo.Student a,
    (
        select @row_number:=0
    ) b
    order by StuNo asc;
    

    中间田野1字段的档期的顺序是int,田野同志2字段的类别是varchar

    结果如下:

    一、row_number

    图片 3

        row_number函数的用途是那么些广阔,这一个函数的功能是为查询出来的每一行记录生成三个序号。row_number函数的用法如上面包车型客车SQL语句所示:

     

     

    福寿康宁rank()排行函数,按学生年龄(StuAge)排序。

    select row_number() over(order by field1) as row_number,* from t_table

    -- @StuAge:=null,设置变量@StuAge的初始值为null
    -- @rank:=0,设置变量@rank的初始值为0
    -- @inRank:=1,设置变量@inRank的初始值为1
    -- if(@StuAge=StuAge,@rank,@rank:=@inRank),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值跳变为@inRank内部计数的值
    -- @inRank:=@inRank+1,每一行自增1,用于实现内部计数
    select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
    from 
    (
        select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@inRank) as row_rank,@inRank:=@inRank+1,@StuAge:=StuAge 
        from demo.Student a,
        (
            select @StuAge:=null,@rank:=0,@inRank:=1 
        ) b 
        order by StuAge asc 
    ) t;
    

        上边的SQL语句的查询结果如图2所示。

    结果如下:

    图片 4

    图片 5

    图2

     

        其中row_number列是由row_number函数生成的序号列。在行使row_number函数是要使用over子句选取对某一列实行排序,然后工夫生成序号。

    实现dense_rank()排行函数,按学生年龄(StuAge)排序。

        实际上,row_number函数生成序号的基本原理是先利用over子句中的排序语句对记录实行排序,然后按着那些顺序生成序号。over子句中的order by子句与SQL语句中的order by子句未有其它涉及,这两处的order by 能够完全两样,如上面包车型客车SQL语句所示:

    -- @StuAge:=null,设置变量@StuAge的初始值为null
    -- @rank:=0,设置变量@rank的初始值为0
    -- if(@StuAge=StuAge,@rank,@rank:=@rank+1),指定排序列的值不变时,@rank的值不变;指定排序列的值变化时,@rank的值自增1
    select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_rank 
    from 
    (
        select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@rank,@rank:=@rank+1) as row_rank,@StuAge:=StuAge
        from demo.Student a,
        (
            select @StuAge:=null,@rank:=0 
        ) b 
        order by StuAge asc 
    ) t;
    

     

    结果如下:

    select row_number() over(order by field2 desc) as row_number,* from t_table order by field1 desc

    图片 6

        上边包车型大巴SQL语句的查询结果如图3所示。

     

    图片 7

    实现row_number() over(partition by colname order by colname)分组排行函数,按学生年龄(StuAge)分组排序。

    图3

    -- @StuAge:=null,设置变量@StuAge的初始值为null
    -- @row_number:=0,设置变量@row_number的初始值为0
    -- if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1),指定排序列的值不变时,@row_number的值自增1;指定排序列的值变化时,@row_number的值等于1
    select t.ID,t.StuNo,t.StuName,t.StuAge,t.row_number 
    from 
    (
        select ID,StuNo,StuName,StuAge,if(@StuAge=StuAge,@row_number:=@row_number+1,@row_number:=1) as row_number,@StuAge:=StuAge 
        from demo.Student a,
        (
            select @StuAge:=null,@row_number:=0 
        ) b 
        order by StuAge asc 
    ) t;
    

        大家能够运用row_number函数来贯彻底追查询表中钦赐范围的笔录,日常将其选取到Web应用程序的分页作用上。下边包车型地铁SQL语句可以查询t_table表中第2条和第3条记录:

    结果如下:

     

    图片 8

    with t_rowtable
    as
    (
        select row_number() over(order by field1) as row_number,* from t_table
    )
    select * from t_rowtable where row_number>1 and row_number < 4 order by field1

     

        上边包车型地铁SQL语句的询问结果如图4所示。

    福寿年高分组聚合字符串,即把内定列的值拼成字符串。
    在SQL Server中时选择了中间变量实现,今后在MySQL中就相比轻易了。
    MySQL提供了三个group_concat()函数,能够把钦命列的值拼成三个字符串,并得以按内定排序方式拼成字符,之间用逗号隔断。如下示例:

    图片 9

    select group_concat(StuNo order by StuNo asc) as column1,group_concat(StuNo order by ID asc) as column2 
    from demo.Student 
    

    图4

    结果如下:

        上边包车型客车SQL语句使用了CTE,关于CTE的介绍将读者参谋《SQL Server2006诗歌(1):使用公用表说明式(CTE)简化嵌套SQL》。
        其余要注意的是,若是将row_number函数用于分页管理,over子句中的order by 与排序记录的order by 应同等,不然生成的序号恐怕不是有续的。
        当然,不使用row_number函数也足以兑现查询钦点范围的记录,正是相比艰苦。平常的办法是使用颠倒Top来兑现,比方,查询t_table表中第2条和第3条记下,能够先查出前3条记下,然后将查询出来的那三条记下按倒序排序,再取前2条记下,最终再将查出来的那2条记下再按倒序排序,便是最后结出。SQL语句如下:

    图片 10

     

     

    select * from (select top 2 * from( select top 3 * from t_table order by field1) a order by field1 desc) b order by field1

    select StuName,group_concat(StuNo order by StuNo asc) as column1,concat('"',group_concat(StuNo order by StuNo asc),'"') as column2 
    from demo.Student 
    group by StuName 
    order by StuAge 
    

        上边包车型地铁SQL语句询问出来的结果如图5所示。

    结果如下:

    图片 11

    图片 12

    图5

     

        这一个查询结果除了未有序号列row_number,其余的与图4所示的询问结果完全等同。

    二、rank

        rank函数考虑到了over子句中排序字段值同样的情况,为了更易于说明难点,在t_table表中再加一条记下,如图6所示。

    图片 13

    图6

        在图6所示的笔录中后三条记下的田野同志1字段值是大同小异的。假设利用rank函数来生成序号,那3条记下的序号是毫无二致的,而第4条记录会依照当前的笔录 数生成序号,后面包车型大巴笔录就那样推算,约等于说,在此个事例中,第4条记下的序号是4,并非2。rank函数的应用形式与row_number函数完全相同,SQL语句如下:

    select rank() over(order by field1),* from t_table order by field1

        上边的SQL语句的询问结果如图7所示。

    图片 14

    图7

    三、dense_rank

        dense_rank函数的效果与利益与rank函数类似,只是在生成序号时是三番三回的,而rank函数生成的序号有比相当的大可能率不三番两次。如上面的事例中若是接纳dense_rank函数,第4条记下的序号应该是2,并非4。如上边包车型客车SQL语句所示:

    select dense_rank() over(order by field1),* from t_table order by field1

        上边的SQL语句的询问结果如图8所示。

    图片 15

    图8

        读者能够相比较图7和图8所示的询问结果有啥样差异

    四、ntile
        ntile函数能够对序号举行分组管理。那就也正是将查询出来的记录集放到内定长度的数组中,各样数组成分存放一定数量的笔录。ntile函数为每条记 录生成的序号就是那条记下全部的数组成分的目录(从1初始)。也能够将每四个抽成记录的数组成分称为“桶”。ntile函数有一个参数,用来钦命桶数。下面包车型地铁SQL语句使用ntile函数对t_table表进行了装桶管理:

    select ntile(4) over(order by field1) as bucket,* from t_table

        上边包车型客车SQL语句的查询结果如图9所示。

    图片 16

    图9

        由于t_table表的记录总量是6,而地方的SQL语句中的ntile函数钦命了桶数为4。

        恐怕某些读者会问这么二个主题素材,SQL Server二〇〇五怎么来支配某一桶应该放多少记录呢?可能t_table表中的记录数有些少,那么我们假使t_table表中有59条记下,而桶数是5,那么每一桶应放多少记录呢?

        实际上通过八个约定就能够爆发三个算法来调整哪一个桶应放多少记录,那五个约定如下:

    1. 号码小的桶放的记录无法小于编号大的桶。也就是说,第1捅中的记录数只可以大于等于第2桶及以往的各桶中的记录。

    2. 享有桶中的记录要么都一样,要么从某多少个笔录很少的桶开端前面全体捅的记录数都与该桶的记录数一样。也正是说,即使有个桶,前三桶的记录数都以10,而第4捅的记录数是6,那么第5桶和第6桶的记录数也必得是6。

        依照上边包车型地铁四个约定,能够摄取如下的算法:

        // mod表示取余,div表示取整 
        if(记录总的数量 mod 桶数 == 0)
        {
            recordCount = 记录总的数量 div 桶数;
            将每桶的记录数都设为recordCount
        } 
        else
        {
            recordCount1 = 记录总的数量 div 桶数 + 1;
            int n = 1;  //  n表示桶中记录数为recordCount1的最大桶数
            m = recordCount1 * n;
            while(((记录总的数量 - m)  mod  (桶数 -  n))  != 0 )
            {
                n++;
                m = recordCount1 * n;
            } 
            recordCount2 = (记录总量 - m) div  (桶数 - n);
            将前n个桶的记录数设为recordCount1
            将n + 1个至前面所有桶的记录数设为recordCount2
        }

        依照下面的算法,假若记录总的数量为59,桶数为5,则前4个桶的记录数皆以12,最终叁个桶的记录数是11。

        要是记录总的数量为53,桶数为5,则前3个桶的记录数为11,后2个桶的记录数为10。

        就拿本例来讲,记录总量为6,桶数为4,则会算出recordCount1的值为2,在得了while循环后,会算出recordCount2的值是1,由此,前2个桶的记录是2,后2个桶的记录是1。

    下一篇:SQL Server二〇〇六杂文(4):按列连接字符串的三种艺术

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:八个排名函数,Server排名函数

    关键词: