七个排行函数,然后在组内排行

图片 28
数据库

近年来在MySQL中蒙受分组排序查询时,突然意识MySQL中尚无row_number()
over(partition by colname)那样的分组排序。
同时由于MySQL中从未看似于SQL
Server中的row_number()、rank()、dense_rank()等排行函数,全部找到以下实现格局,在此轻便记录一下。

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

今天在采纳多字段去重时,由于有个别字段有二种可能性,只需依照局地字段打开去重,在网络看看了rownumber()
over(partition by col一 order by
col二)去重的不贰诀要,很科学,在此记录分享下:
  row_number() OVE奥迪Q7 ( PARTITION BY COL1 ORubiconDE福睿斯 BY COL2)
表示依据COL一分组,在分组内部依据COL二排序,而此函数总结的值就象征每组内部排序后的种种编号(组内再而三的唯1的).
  与rownum的界别在于:使用rownum举办排序的时候是先对结果集出席伪列rownum然后再举办排序,而此函数在包括排序从句后是先排序再总计行号码.

row_number()rownum多数,功用越来越强一些(能够在种种分组内从1开时排序).
rank()是跳跃排序,有七个第1名时接下去正是第陆名(同样是在1一分组内).
dense_rank()l是连连排序,有多少个第1名时还是跟着第三名。比较之下row_number是从未有过重复值的.
lag(arg1,arg2,arg3):
  arg1是从别的行再次来到的表明式
  arg2是期望物色的此时此刻行分区的偏移量。是三个正的偏移量,是1个往回检索在此以前的行的数目。
  arg3是在arg二意味着的多少超越了分组的限制时再次回到的值。

函数语法:
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
首要的效果是计量1组数值中的排序值。
【参数】dense_rank与rank()用法非常,
【区别】dence_rank在并列关系是,相关品级不会跳过。rank则跳过
rank()是跳跃排序,有五个第二名时接下去便是第四名(同样是在每家每户分组内)
dense_rank()l是连连排序,有八个第二名时依然跟着第3名。
【表达】Oracle分析函数

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【作用】表示依据COL一分组,在分组内部依据COL二排序,而这些值就代表每组内部排序后的逐条编号(组内一连的当世无双的)
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

 

如上内容摘要自:

 

正文为原创,如需转发,请声明我和出处,多谢!
上一篇:SQL
Server2005随想(贰):公用表表明式(CTE)的递归调用

 

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

 

事先用过row_number(),rank()等排序与over( partition by … OEnclaveDEPAJERO BY
…),那四个相比较好精晓: 先分组,然后在组内排名。

前些天突然境遇sum(…) over( partition by … OLX570DE奥德赛 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 …
),同第叁点中的排序求和公理,只是范围限制在组内

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=壹 )

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 /*取各个分组内部序号=一 的消息*/

 

sql依据某多少个字段重复只取第壹条数据
利用分析函数row_number() over (partiion by … order by
…)来拓展分组编号,然后取分组标号值为一的笔录就能够。目前主流的数据库都有支撑分析函数,很好用。
在那之中,partition by
是点名按如何字段实行分组,这一个字段值同样的笔录就要协同编号;order
by则是点名在一样组中进行编号时是依据什么样的逐条。
示范(SQL Server 200伍或上述适用):

select s.*  
from ( 
    select *, row_number() over (partition by [手机号] order by [店铺]) as group_idx  
    from table_name
) s
where s.group_idx = 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

 

    排名函数是SQL
Server200伍新加的效劳。在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

图片 17

4. ntile   
   
上边分别介绍一下那四个排行函数的成效及用法。在介绍以前假如有两个t_table表,表结构与表中的多少如图①所示:

 

图片 18

实现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;

里头田野同志一字段的门类是int,田野同志2字段的项目是varchar

结果如下:

一、row_number

图片 19

    row_number函数的用途是足够布满,那么些函数的功能是为查询出来的每1行记录生成三个序号。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语句的查询结果如图二所示。

结果如下:

图片 20

图片 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语句的询问结果如图叁所示。

 

图片 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语句的询问结果如图四所示。

福寿齐天分组聚合字符串,即把钦赐列的值拼成字符串。
在SQL Server中时利用了中档变量落成,未来在MySQL中就比较轻松了。
MySQL提供了3个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二〇〇七散文(壹):使用公用表表明式(CTE)简化嵌套SQL》。
    其余要小心的是,如若将row_number函数用于分页管理,over子句中的order by 与排序记录的order by 应1律,不然生成的序号只怕不是有续的。
    当然,不使用row_number函数也得以落成查询钦定范围的笔录,正是相比费心。一般的法子是运用颠倒Top来贯彻,例如,查询t_table表中第1条和第壹条记下,能够先查出前叁条记下,然后将查询出来的这三条记下按倒序排序,再取前二条记下,最终再将查出来的那二条记下再按倒序排序,正是最终结出。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,其他的与图四所示的查询结果完全等同。

二、rank

    rank函数思虑到了over子句中排序字段值相同的情状,为了更便于表达难点,在t_table表中再加一条记下,如图陆所示。

图片 29

图6

   
在图6所示的笔录中后3条记下的田野一字段值是壹律的。倘使选取rank函数来生成序号,那三条记下的序号是同等的,而第5条记录会依照近日的笔录
数生成序号,前边的笔录由此及彼,也正是说,在这些事例中,第四条记下的序号是四,而不是二。rank函数的运用办法与row_number函数完全同样,SQL语句如下:

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

    上面的SQL语句的查询结果如图柒所示。

图片 30

图7

三、dense_rank

    dense_rank函数的功力与rank函数类似,只是在生成序号时是连连的,而rank函数生成的序号有希望不总是。如上边的例子中只要运用dense_rank函数,第伍条记下的序号应该是2,而不是四。如下边包车型客车SQL语句所示:

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

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

图片 31

图8

    读者可以相比较图七和图八所示的询问结果有怎么着两样

四、ntile
   
ntile函数能够对序号举行分组管理。那就相当于将查询出来的记录集放到钦赐长度的数组中,每3个数组成分存放一定数额的记录。ntile函数为每条记
录生成的序号就是那条记下全体的数组成分的目录(从1始发)。也足以将每贰个分红记录的数组成分称为“桶”。ntile函数有三个参数,用来钦命桶数。上面包车型地铁SQL语句使用ntile函数对t_table表实行了装桶管理:

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

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

图片 32

图9

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

    或然某个读者会问这么贰个主题材料,SQL
Server200伍怎么来决定某一桶应该放多少记录呢?恐怕t_table表中的记录数有些少,那么我们就算t_table表中有5玖条记下,而桶数是伍,那么每一桶应放多少记录呢?

    实际上通过五个约定就能够发生叁个算法来决定哪三个桶应放多少记录,那五个约定如下:

1. 编号小的桶放的记录不可能小于编号大的桶。也便是说,第二捅中的记录数只可以大于等于第一桶及之后的各桶中的记录。

二. 怀有桶中的记录要么都壹模同样,要么从某一个记下较少的桶开端前面全体捅的记录数都与该桶的记录数同样。也正是说,要是有个桶,前③桶的记录数都以10,而第陆捅的记录数是六,那么第肆桶和第六桶的记录数也非得是6。

    根据地方的多个约定,能够得出如下的算法:

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

    依照下边的算法,要是记录总量为5玖,桶数为伍,则前陆个桶的记录数都是12,最终一个桶的记录数是1壹。

    假如记录总量为5叁,桶数为五,则前二个桶的记录数为11,后三个桶的记录数为拾。

    就拿本例来讲,记录总量为6,桶数为四,则会算出recordCount壹的值为2,在收尾while循环后,会算出recordCount2的值是一,因而,前一个桶的记录是2,后2个桶的笔录是一。

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图