各个组有编号,编号从一开始。对于每一个行,NTILE函数将返回此行所属的组的编号。如果分区的行数不能被整数整除,则将导致一个成员有两种大小不同的组。按照OVER子句指定的顺序,较大的组排在较小的组前面。用NTILE函数计算排名值与其它方法一样简单(分区排名方案和排名值效率分析【图文+测试代码】),唯一的区别在于,NTILE函数接受一个表示组的数量的参数,而其它的方法是没有参数的。
SQL代码和效果如下:
- Code
- ifOBJECT_ID('Sales')isnotnull
- droptableSales;
- createtableSales
- (
- empidvarchar(10)notnullprimarykey,
- qtyintnotnull
- )
- insertintoSales(empid,qty)values('A',300);
- insertintoSales(empid,qty)values('B',100);
- insertintoSales(empid,qty)values('C',200);
- insertintoSales(empid,qty)values('D',200);
- insertintoSales(empid,qty)values('E',250);
- insertintoSales(empid,qty)values('F',300);
- insertintoSales(empid,qty)values('H',250);
- insertintoSales(empid,qty)values('I',250);
- insertintoSales(empid,qty)values('J',100);
- insertintoSales(empid,qty)values('K',200);
- insertintoSales(empid,qty)values('G',100);
- --------------------------------------------------
- selectempid,qty,NTILE(9)over(orderbyqty)astilefromSales
执行结果效果图:
那么在没有出现SQLServer2005之前我们又是怎样做来达到这样的效果的呢?下面我将给大家介绍两种方法来达到这个需求:
方法一:首先计算表的行号(排名值),根据指定的组的数量得到每组内的记录数量。然后利用组号计算公式:(行号-1)/组大小+1,返回每条记录的组号。
SQL代码如下:
- Code
- eclare@numtilesint;
- set@numtiles=9;--组数
- selectempid,qty,CAST((rn-1)/tilesize+1asint)astile
- from(selectempid,qty,rn,1.0*numrows/@numtilesastilesizefrom(selectempid,qty,(selectCOUNT(*)fromSalesasS2whereS2.qty
方法二:首先计算表的行号(排名值),根据指定的组的数量得到每组内的记录数量。然后利用下面组号计算公式,返回每条记录的组号。
If(行号<=(组大小+1)*剩余行数)then
组号=(行号-1)/(组大小+1)+1
Else
组号=(行号-剩余行数-1)/组大小+1
SQL代码如下:
- Code
- declare@numtileint;
- set@numtile=9;--组数
- selectempid,qty,rn,
- casewhenrn<=(tilesize+1)*remainder
- then(rn-1)/(tilesize+1)+1
- else(rn-remainder-1)/(tilesize)+1
- endastiles
- from
- (
- selectempid,qty,rn,numrows/@numtileastilesize,numrows%@numtileasremainder
- from
- (
- selectempid,qty,(selectCOUNT(*)fromSalesasS2whereS2.qty
- )asD1
- )asD2orderbyqty,empid
【编辑推荐】