多个排行函数,分组计算

近些日子在MySQL中遇见分组排序查询时,突然发掘MySQL中从不row_number() over(partition by colname)那样的分组排序。
况且由于MySQL中未有相近于SQL Server中的row_number()、rank()、dense_rank()等排行函数,所有找到以下达成情势,在此轻易记录一下。

本文为原创,如需转发,请评释笔者和出处,谢谢!
上一篇:SQL Server二〇〇五杂文(2):公用表表达式(CTE)的递归调用

ROWNUMBER() OVER( PARTITION BY COL1 ORDER BY COL2)用法

今日在使用多字段去重时,由于有个别字段有种种也许,只需依照一些字段张开去重,在互连网来看了rownumber() over(partition by col1 order by col2)去重的方法,很准确,在此记录分享下:
  row_number() OVERAV4 ( PARTITION BY COL1 O瑞虎DE昂Cora BY COL2) 表示遵照COL1分组,在分组内部依据COL2排序,而此函数计算的值就意味着每组内部排序后的逐个编号(组内接二连三的独一的).
  与rownum的分歧在于:使用rownum进行排序的时候是先对结果集到场伪列rownum然后再张开排序,而此函数在蕴涵排序从句后是先排序再计算行号码.

row_number()rownum基本上,效用越来越强一些(能够在各个分组内从1开时排序).
rank()是跳跃排序,有三个第二名时接下去便是第四名(同样是在逐条分组内).
dense_rank()l是连连排序,有两个第二名时还是跟着第三名。相比较之下row_number是从未重复值的.
lag(arg1,arg2,arg3):
  arg1是从其余行重回的表明式
  arg2是意在物色的脚下行分区的偏移量。是三个正的偏移量,是三个往回检索以前的行的数目。
  arg3是在arg2意味的多少赶过了分组的限量时回来的值。

函数语法:
OPAP函数语法四部分:
1.function 本人用于对窗口中的数据开展操作;
2.partitioning clause 用于将结果集分区;
3.order by clause 用于对分区中的数据实行排序;
4.windowing clause 用于定义function在其上操作的行的集合,即function所影响的限量;

RANK()
dense_rank()
【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【作用】聚合函数RANK 和 dense_rank 主要的遵守是计算一组数值中的排序值。
【参数】dense_rank与rank()用法十一分,
【区别】dence_rank在并列关系是,相关品级不会跳过。rank则跳过
rank()是跳跃排序,有多个第二名时接下去正是第四名(一样是在依次分组内)
dense_rank()l是三番四回排序,有八个第二名时依然跟着第三名。
【表达】Oracle剖判函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【作用】表示依照COL1分组,在分组内部依据COL2排序,而那些值就代表每组内部排序后的逐一编号(组内三翻五次的天下无双的)
row_number() 重返的第一是“行”的音信,并不曾排行
【参数】
【表明】Oracle剖析函数

十分重要意义:用于取前几名,或然最终几名等
sum(...) over ...
【成效】一连求和剖判函数
【参数】具体参示例
【表明】Oracle深入分析函数

lag()lead()
【语法】
lag(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
LEAD(EXPR,<OFFSET>,<DEFAULT>) OVER ( [query_partition_clause] order_by_clause )
【效能】表示依照COL1分组,在分组内部依照COL2排序,而以此值就意味着每组内部排序后的逐条编号(组内一而再的独一的)
lead () 下一个值 lag() 上一个值

【参数】
EXP逍客是从别的行重返的表达式
OFFSET是缺省为1 的正数,表示相对行数。希望物色的当下行分区的偏移量
DEFAULT是在OFFSET表示的数额超过了分组的限量时回来的值。
【表明】Oracle深入分析函数

---TEST FOR ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
DROP TABLE TEST_Y
CREATE TABLE TEST_Y(
       ID VARCHAR2 (32) PRIMARY KEY ,
       NAME VARCHAR2 (20),
       AGE NUMBER(3 ),
       DETAILS VARCHAR2 (1000)
);
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',20 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '海子',30 ,'面朝大海,春暖花开');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '贝多芬',43 ,'致爱丽丝');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '普希金',34 ,'假如生活欺骗了你');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '杨过',23 ,'黯然销魂掌');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '小龙女',32 ,'神雕侠侣');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '李清照',21 ,'寻寻觅觅、冷冷清清');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '周芷若',18 ,'峨眉');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '赵敏',18 ,'自由');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',20 ,'倚天屠龙记');
INSERT INTO TEST_Y VALUES(SYS_GUID(), '张无忌',30 ,'倚天屠龙记');

SELECT * FROM TEST_Y;


----1. ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
---查询所有姓名,如果同名,则按年龄降序
SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

图片 1

----通过上面的语句可知,ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)中是按照NAME字段分组,按AGE字段排序的。
----如果只需查询出不重复的姓名即可,则可使用如下的语句
SELECT * FROM (SELECT NAME,AGE,DETAILS ,ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY AGE DESC)RN FROM TEST_Y )WHERE RN= 1;

图片 2

----由查询结果可知,姓名相同年龄小的数据被过滤掉了;可以使用ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)对部分子弹进行去重处理


----2.RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----跳跃排序
SELECT NAME ,AGE,DETAILS , RANK() OVER (PARTITION BY NAME ORDER BY AGE DESC) FROM TEST_Y;

图片 3

----由查询结果可知,相同的并列,下一个则跳跃到并列所替的序列后:如有两个并列1,那么下一个则直接排为3,跳过2;

----3.DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)
----连续排序,当有多个并列时,下一个仍然连续有序

图片 4

----由查询结果可知,当两个并列为1时,下一个仍连续有序为2,不跳跃到3

 Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现,不过使用LAG和LEAD有更高的效率.
  Lag和Lead偏移量函数,其用途是:可以查出同一字段下一个值或上一个值,并作为新列存在表中.
-----4.LAG(exp_str,offset,defval) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的下一个exp_str;defval当该函数无值可用的情况下返回该值。
(1) SELECT NAME ,AGE,DETAILS, LAG(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_

图片 5

(2) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

图片 6

(3) SELECT NAME ,AGE,DETAILS, LAG(NAME ,2, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

图片 7

----5.LEAD(EXP_STR,OFFSET,DEFVAL) OVER(PARTITION BY NAME ORDER BY AGE)
-----exp_str 返回显示的字段;offset是exp_str字段的偏移量,默认是1,如offset=1表示返回当前exp_str的上一个exp_str;
-----defval当该函数无值可用的情况下返回该值。
(1)SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM 

图片 8

(2) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,2, 'sue') OVER (PARTITION BY NAME ORDER BY AGE DESC ) FROM TEST_Y

图片 9

(3) SELECT NAME ,AGE,DETAILS, LEAD(NAME ,1, 'sue') OVER (ORDER BY AGE DESC ) FROM TEST_Y;

图片 10

-----6.SUM(COL1) OVER([PARTITION BY COL2 ] [ORDER BY COL3])
(1) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PART

图片 11

(2) SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( PAR

图片 12

(3)SELECT NAME ,AGE,DETAILS,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY AGE DESC),SUM (AGE) OVER( ORDE

图片 13

(4) SELECT NAME ,AGE,DETAILS, SUM(AGE) OVER ()  FROM TEST_Y;

图片 14

 

上述内容摘要自:

 

over()解析函数用于总计基于组的某种聚合值,它和聚合函数的不相同之处是:对于种种组重临多行,而聚合函数对于每种组只再次回到一行。
例子:

 

    排名函数是SQL Server贰零零伍新加的法力。在SQL Server二〇〇七中有如下多少个排行函数:

sum(x) over( partition by y ORDER BY z ) 分析

 

之前用过row_number(),rank()等排序与over( partition by ... OLacrosseDE奥迪Q5 BY ...),那四个相比较好精晓: 先分组,然后在组内排行。

前日卒然遇上sum(...) over( partition by ... OENVISIONDE凯雷德 BY ... ),居然搞不清除怎么实施的,所以查了些资料,做了下实际操作。

  1. 从最简便易行的上马

  sum(...) over( ),对具有行求和

  sum(...) over( order by ... ),和 = 第一行 到 与方今行同序号行的最后一行的保有值求和,文字不太好领悟,请看下图的算法深入分析。

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序,求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序,求和 C列所有值

sum() over()

图片 15

  1. 与 partition by 结合

  sum(...) over( partition by... ),同组内所行求和

  sum(...) over( partition by... order by ... ),同第1点中的排序求和规律,只是范围界定在组内

with aa as
( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
)
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
  FROM aa;

view sql

图片 16

 

如上内容摘要自:

 

案例:

有天地球表面CMSocial,圈子成员表CMSocialMember,圈子审查表CMSocialCheck,在那之中世界调查被拒绝的话,修改消息后得以再一次提交核查,也正是说圈子能够生成多条世界调查音讯。

假定要查询某客商的凡事天地,同不经常候获取在那之中每条世界对应的这几天一条调查景况?(假如某客商MemberID=1 )

SQL语句能够那样写:

SELECT 
S.CMSocialID,
S.SocialName,
S.SocialDescription,
S.SocialLogo,
S.SocialAuthority,
S.Integral,
S.SocialState,
S.IsAvailable,
SC.CheckState,
SC.Notes,
SM.CMSocialMemberID,
SM.MemberID,
SM.MemberName,
SM.MemberIntegral,
SM.EnterTime,
SM.MemberState,
SM.MemberRank,
SM.IsRecommend
FROM (SELECT * FROM CMSocialMember WHERE MemberID=1 AND IsDelete<>1 AND IsAvailable=1) AS SM 
LEFT JOIN CMSocial AS S ON S.CMSocialID=SM.CMSocialID 
LEFT JOIN (
SELECT *
FROM ( 
    SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*  /* 根据 CMSocialID 分组,CreateTime倒序,生成分组内部序号 */
    FROM CMSocialCheck WHERE IsDelete<>1
    ) AS SCsub WHERE SCsub.group_index=1 /*取每个分组内部序号=1 的信息*/
) AS SC ON SC.CMSocialID=S.CMSocialID

 

注意:

SELECT ROW_NUMBER() OVER (PARTITION BY CMSocialID ORDER BY CreateTime DESC) AS group_index ,*      /* 遵照 CMSocialID 分组,CreateTime倒序,生成分组内部序号 */
FROM CMSocialCheck WHERE IsDelete<>1
) AS SCsub WHERE SCsub.group_index=1 /*取每一种分组内部序号=1 的新闻*/

 

sql遵照某贰个字段重复只取第一条数据
应用深入分析函数row_number() over (partiion by ... order by ...)来开展分组编号,然后取分组标号值为1的笔录就能够。方今主流的数据库都有援助深入分析函数,很好用。
中间,partition by 是内定按什么字段实行分组,那个字段值同样的记录将要共同编号;order by则是点名在同样组中开展编号时是依据什么的种种。
身体力行(SQL Server 二零零七或上述适用):

select s.*  
from ( 
    select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx  
    from table_name
) s
where s.group_idx = 1

 

主表1条数据,对应子表,附表多条数据,取独一:

DECLARE @Status INT;
SET @Status=1;
SELECT  p.CFProjectID AS ID,pc.Status AS StatusID,* FROM dbo.CFProject p
LEFT JOIN (
    select s.*  
    from ( 
        select *, row_number() over (partition by [SourceProjectID] order by [CFProjectID]) as group_idx  
        from dbo.CFProjectToCrowdSpace WHERE [Status]=@Status OR @Status IS NULL
    ) s
    where s.group_idx = 1
) pc ON pc.SourceProjectID=p.CFProjectId
WHERE p.CreatorID=100273  AND p.Status=3  AND p.OrganizationID=180  ORDER  BY p.CreationTime DESC

 

select *from (select name,class,score,rank() over(partition by class order by score desc) mm from t2 ) where mm=1

第一创制三个表并插入测验数据。

1. row_number

透过class班级举办分组,并根据score分数进行排序,用rank()函数排序方法为mm列赋予序号,然后mm=1就可以找到每组的头名,当然能够依靠score就行倒序可以找到最终一名。

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

row_number() over(partition by ... order by ...)

测量检验数据如下:

3. dense_rank

简易的说row_number()从1发轫,为每一条分组记录再次来到贰个数字, row_number() over(order by score desc)是先把score 列降序,再为降序现在的没条xlh记录重返二个序号。(若无分组能够知晓成将全方位结果作为多个分组)

图片 17

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

row_number() over(partition by class order by score desc)表示依照class分组,在分组内部依据 score 排序,而此函数总计的值就象征每组内部排序后的次第编号(组内一连的独一的)

 

图片 18

rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)

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

图1

用作分数函数中关于排序的rank(),dense_rank(),row_number()。

-- @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

rank() over是的效能是摸清钦点条件后举办一个排行,可是有三个特点。固然是对学生排行,那么实用那些函数,战绩同样的两名是同等对待(排行为1,2,2,4)

结果如下:

一、row_number

dense_rank()的效率和rank()很像,独一差异就是,同样战表并列以往,下一人同学并不空出并列所占的排名(排名为1,2,2,3)

图片 19

    row_number函数的用途是那一个广阔,那些函数的功能是为查询出来的每一行记录生成二个序号。row_number函数的用法如下边包车型大巴SQL语句所示:

row_number()就不雷同了,它和地点三种的分别就很刚烈了,那个函数无需思量是或不是并列,哪怕依照法则查询出来的数值一样也会进展接二连三排行。

 

 

对于多表查询,可以为空置加上贰个肯定来体现查询数据为空的多寡。

兑现rank()排名函数,按学生年龄(StuAge)排序。

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

case when score is null then 1 else rank() over (partition by class order by score desc ) end as mm

-- @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所示。

其余常用的剖判函数:

结果如下:

图片 20

count() over(partition by ... order by ...) max() over(partition by ... order by ...) min() over(partition by ... order by ...) sum() over(partition by ... order by ...) avg() over(partition by ... order by ...) first_value() over(partition by ... order by ...) last_value() over(partition by ... order by ...) lag() over(partition by ... order by ...) lead() over(partition by ... order by ...)

图片 21

图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

图片 22

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

 

图片 23

实现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条记录:

结果如下:

 

图片 24

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()函数,可以把钦命列的值拼成二个字符串,并得以按钦命排序情势拼成字符,之间用逗号隔开分离。如下示例:

图片 25

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语句如下:

图片 26

 

 

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所示。

结果如下:

图片 27

图片 28

图5

 

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

二、rank

    rank函数思索到了over子句中排序字段值一样的事态,为了更易于表达难点,在t_table表中再加一条记下,如图6所示。

图片 29

图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所示。

图片 30

图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所示。

图片 31

图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所示。

图片 32

图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):按列连接字符串的三种艺术

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:多个排行函数,分组计算

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。