SQL Server 2005 T-SQL Query学习(三)

栏目:MSSQL 来源:网络 关注:0 时间:2019-08-21

利用ROW_NUMBER()进行高效率的分页。

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;

本文标题:SQL Server 2005 T-SQL Query学习(三)
本文地址:http://www.q0738.com/mssql/1370.html