大发体育娱乐在线-大发体育娱乐官方网站-大发体育娱乐登录网址
做最好的网站

多个排行函数,Server排行函数

来源:http://www.dfwstonefabricators.com 作者:数据库 人气:158 发布时间:2019-10-12
摘要:不久前在MySQL中相遇分组排序查询时,忽地发掘MySQL中尚无row_number()over(partition by colname)那样的分组排序。 再者鉴于MySQL中尚无像样于SQLServer中的row_number()、rank()、dense_rank()等排名函数,

不久前在MySQL中相遇分组排序查询时,忽地发掘MySQL中尚无row_number() over(partition by colname)那样的分组排序。
再者鉴于MySQL中尚无像样于SQL Server中的row_number()、rank()、dense_rank()等排名函数,全部找到以下完成格局,在那轻巧记录一下。

本文为原创,如需转发,请表明作者和出处,多谢!
上一篇:SQL Server二〇〇五随想(2):公用表表明式(CTE)的递归调用

 

    排行函数是SQL Server二零零七新加的作用。在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,田野(field)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 Server二〇〇七随笔(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 Server2007怎么来决定某一桶应该放多少记录呢?或许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):按列连接字符串的三种艺术

本文由大发体育娱乐在线发布于数据库,转载请注明出处:多个排行函数,Server排行函数

关键词:

最火资讯