NTILE函数在SQL Server 2000中的实现方法

众所周知，NTILE函数是SQL Server 2005的新特性之一，用于将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。如果分区的行数不能被整数整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。用NTILE函数计算排名值与其它方法一样简单（分区排名方案和排名值效率分析【图文+测试代码】 ），唯一的区别在于，NTILE函数接受一个表示组的数量的参数，而其它的方法是没有参数的。

SQL代码和效果如下：      

Code
if OBJECT_ID ('Sales') is not null
    drop table Sales;
create table Sales
(
    empid varchar(10) not null primary key,
    qty int not null
)
insert into Sales (empid ,qty ) values ('A',300);
insert into Sales (empid ,qty ) values ('B',100);
insert into Sales (empid ,qty ) values ('C',200);
insert into Sales (empid ,qty ) values ('D',200);
insert into Sales (empid ,qty ) values ('E',250);
insert into Sales (empid ,qty ) values ('F',300);
insert into Sales (empid ,qty ) values ('H',250);
insert into Sales (empid ,qty ) values ('I',250);
insert into Sales (empid ,qty ) values ('J',100);
insert into Sales (empid ,qty ) values ('K',200);
insert into Sales (empid ,qty ) values ('G',100);


--------------------------------------------------
select empid,qty,NTILE (9) over (order by qty) as tile from Sales 

 执行结果效果图：
      
      那么在没有出现SQL Server 2005之前我们又是怎样做来达到这样的效果的呢？下面我将给大家介绍两种方法来达到这个需求：

方法一：首先计算表的行号（排名值），根据指定的组的数量得到每组内的记录数量。然后利用组号计算公式：（行号-1）/组大小+1，返回每条记录的组号。

SQL代码如下：      

Code
eclare @numtiles int;
set @numtiles =9;--组数

select empid,qty,CAST ((rn-1)/tilesize +1 as int ) as tile
    from (select empid,qty,rn,1.0*numrows/@numtiles as tilesize from (select empid,qty,(select COUNT (*) from Sales as S2 where S2 .qty <S1.qty or S2 .qty =S1.qty and S2 .empid <=S1.empid) as rn,(select COUNT (*) from Sales ) as numrows from Sales as S1 ) as D1) as D2 order by qty,empid ;

方法二：首先计算表的行号（排名值），根据指定的组的数量得到每组内的记录数量。然后利用下面组号计算公式，返回每条记录的组号。

If(行号<=（组大小+1）* 剩余行数) then

      组号=（行号-1）/（组大小+1）+1

Else

      组号=（行号-剩余行数-1）/组大小+1

SQL代码如下：      

Code
declare @numtile int;
set @numtile =9;--组数

select empid ,qty,rn,
    case when rn<=(tilesize+1)*remainder
            then (rn-1)/(tilesize+1)+1
         else (rn-remainder-1)/(tilesize)+1
    end as tiles
    from 
    (
        select empid,qty,rn,numrows/@numtile as tilesize,numrows%@numtile as remainder 
            from 
            (
                select empid,qty,(select COUNT (*) from Sales as S2 where S2.qty <S1.qty or S2.qty =S1.qty and S2.empid <=S1.empid) as rn ,(select COUNT (*) from Sales ) as numrows from Sales as S1 
            ) as D1
    ) as D2 order by qty,empid

转载于:https://www.cnblogs.com/chjw8016/archive/2009/08/05/1539473.html