SqlServer 2005 T-SQL Query 学习笔记（3）

ad hoc paging：

就是指用页面的序号和页面的大小请求一个单独的页面。下面是例子。

declare @pagesize as int, @pagenum as int;
set @pagesize = 5;
set @pagenum = 2;

with salescte as
(
 select row_number() over(order by qty, empid) as rownum,
  empid, mgrid, qty
 from dbo.sales
)
select rownum, empid, mgrid, qty
from salescte
where rownum > @pagesize * (@pagenum-1)
 and rownum <= @pagesize * @pagenum
order by rownum;

说明：在上个例子中，其实sql只审视了10行（2*5），也就是说，查看n页的话，sql只查到n的页的数据，n页后面的数据一概不查看。

另外，每当移动一页，都会把这页放进缓存里，因此每次查询，就是逻辑查询（缓存）+物理查询的过程。物理查询只需要查询新请求的页即可，其他全部在缓存里执行，这样大大加快了查询速度。

multipage access：

如果结果集不是很大，而且分了多个请求页面，请求也不向前移动，那么这是一个好的方案：首先在一个表里使用row_number具体化所有的页，然后创建一个群集索引。下面是例子。

 

首先创建按row_number把列编好，

 

select row_number() over(order by qty, empid) as rownum, empid, mgrid, qty into #salesrn from dbo.sales;

create unique clustered index idx_rn on #salesrn(rownum);

 

然后直接按rownum查询，

declare @pagesize as int, @pagenum as int; set @pagesize = 5; set @pagenum = 2; 
select rownum, empid, mgrid, qty from #salesrn where rownum between @pagesize * 
(@pagenum-1) + 1 and @pagesize * @pagenum order by rownum;

 

rank & dense rank

这2个函数和row_number的区别是：row_number在order by的条件里有重复行存在的话，是把这些重复行也按index排列的，但是rank和dense rank总是确定的，即只要是order by重复的行，他们是统一index的。

 

rank和dense_rank的区别是，rank是如果上级的index和下级的index有可能不是+1关系，是按下级真正处于列里的位置进行index，而dense_rank是按照跟上级的index+1的关系进行的编码。

 

比如：

select empid, qty, rank() over(order by qty) as rnk, dense_rank() over(order by qty) as drnk from dbo.sales order by qty;

ntile

ntile的用法和其他的rank函数一样，只不过它可以传入一个参数，用来决定最大的index是多少：它会按行数进行除法，然后平均分配行数进行index的标示。

比如，如果有11列，那么首先11/3=3，3列一组作为一个index，然后，11%3=2，这2列会分别加在前面的2组上。

比如,

select empid, qty,
 case ntile(3) over(order by qty, empid)
  when 1 then 'low'
  when 2 then 'medium'
  when 3 then 'high'
 end as lvl
from dbo.sales
order by qty, empid;