SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記（3）

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;

說(shuō)明：在上個(gè)例子中，其實(shí)SQL只審視了10行（2*5），也就是說(shuō)，查看N頁(yè)的話，SQL只查到N的頁(yè)的數(shù)據(jù)，N頁(yè)后面的數(shù)據(jù)一概不查看。

另外，每當(dāng)移動(dòng)一頁(yè)，都會(huì)把這頁(yè)放進(jìn)緩存里，因此每次查詢，就是邏輯查詢（緩存）+物理查詢的過(guò)程。物理查詢只需要查詢新請(qǐng)求的頁(yè)即可，其他全部在緩存里執(zhí)行，這樣大大加快了查詢速度。

MULTIPAGE ACCESS：

如果結(jié)果集不是很大，而且分了多個(gè)請(qǐng)求頁(yè)面，請(qǐng)求也不向前移動(dòng)，那么這是一個(gè)好的方案：首先在一個(gè)表里使用ROW_NUMBER具體化所有的頁(yè)，然后創(chuàng)建一個(gè)群集索引。下面是例子。

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);

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;

SELECT empid, qty, RANK() OVER(ORDER BY qty) AS rnk, DENSE_RANK() OVER(ORDER BY qty) AS drnk FROM dbo.Sales ORDER BY qty;

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;