--排名函數(shù)
/**/
--Sql Server 2005排名函數(shù)只能用于查詢的SELECT 和 ORDER BY 子句中。排名計(jì)算（無(wú)論你使用什么方法）的最佳索引是在分區(qū)列、排序列、覆蓋列上創(chuàng)建的索引。
--行號(hào)：是指按指定順序?yàn)椴樵兘Y(jié)果集中的行分配的連續(xù)整數(shù)。在后面的節(jié)中，將描述Sql Server 2005與之前版本中計(jì)算行號(hào)的工具與方法。
SELECT empid,qty,ROW_NUMBER()OVER(ORDER BY qty)AS RowNum
FROM [Sales]
ORDER BY [qty]
--確定性
SELECT empid,qty,ROW_NUMBER()OVER(ORDER BY qty)AS RowNum,ROW_NUMBER()OVER(ORDER BY qty,empid)AS RowNum2
FROM [Sales]
ORDER BY qty,empid
--分區(qū)
SELECT mgrid,empid,qty,ROW_NUMBER()OVER(PARTITION BY mgrid ORDER BY qty,empid)AS RowNum
FROM [Sales]
ORDER BY mgrid,qty
--=====之前2000版本基于集合的方法實(shí)現(xiàn)
--唯一排序列：給定一個(gè)唯一的分區(qū) + 排序列組合 (如下例的唯一的分區(qū)是empid,排序列empid
SELECT empid,(SELECT COUNT(*) FROM [Sales] AS s2 WHERE s2.empid<=s1.empid)AS rowNum
FROM [Sales] s1 ORDER BY [empid]
--查看執(zhí)行計(jì)劃，（順序是從上至下，從右至左看）會(huì)發(fā)現(xiàn)有兩個(gè)不同的運(yùn)算符使用了聚集索引。第一個(gè)是完整掃描以返回所有的行(這個(gè)例子是11行)；第二個(gè)運(yùn)算符先為每個(gè)外部執(zhí)行查找，再執(zhí)行局部掃描，以完成統(tǒng)計(jì)。還記得嗎？影響數(shù)據(jù)處理查詢性能的主要因素通常中I/O。這種方式在小數(shù)據(jù)量時(shí)不明顯,但當(dāng)數(shù)據(jù)量較大時(shí)(大于千條),由于每一條記錄都需要將全部表掃描一次,使用這種方法掃描的總行數(shù)將是1+2+3+N，對(duì)于整體上100000行的表，你一共會(huì)掃描50005000行。順便提一下，計(jì)算前N個(gè)正整數(shù)之各的公式是(N+N的平方)/2。
--看示例即了解到的.
USE [AdventureWorks]
SET STATISTICS TIME ON
SELECT salesorderid,ROW_NUMBER()OVER(ORDER BY salesorderid)AS rownum
FROM sales.[SalesOrderHeader]
SELECT salesorderid,(SELECT COUNT(*) FROM sales.[SalesOrderHeader] b WHERE b.salesorderid<=a.salesorderid)AS rownum
FROM sales.[SalesOrderHeader] a
ORDER BY [salesorderid]
/* 結(jié)果:
(31465 行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 47 毫秒，占用時(shí)間 = 674 毫秒。
(31465 行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 133094 毫秒，占用時(shí)間 = 134030 毫秒。
可想而知,新排名函數(shù)的憂化方面是很不錯(cuò)的.
*/
--不唯一排序列和附加屬性：當(dāng)排序列不唯一時(shí)，你可以通過(guò)引入一個(gè)附加屬性使它唯一。以下查詢按qty和empid的順序生成行號(hào)
SELECT empid,qty,(SELECT COUNT(*) FROM [Sales] s2 WHERE s2.qty<s1.qty OR ((s2.qty=s1.qty AND s2.empid<=s1.empid)))AS rowNum
FROM [Sales] s1 ORDER BY qty,empid
--以上示例qty為排序列，empid為附加列。要統(tǒng)計(jì)具有相同或更小排序列表值（qty+empid)的行，在子查詢中使用以下表達(dá)式
--inner_qty < outer_qty OR (inner_qty=outer_qty AND inner_empid <= outer_empid)
--沒(méi)有附加屬性的不唯一序列：當(dāng)你要根據(jù)不唯一排序列分配行號(hào)，而且不使用附加屬性時(shí)，在SQL Server 2005之前的版本中用基于集合的方法解決該問(wèn)題就更復(fù)雜了。通過(guò)以下代碼清單創(chuàng)建并填充該表。
IF OBJECT_ID('T1')IS NOT NULL
DROP TABLE T1
CREATE TABLE T1(col1 VARCHAR(5))
INSERT INTO t1(col1) VALUES('A')
INSERT INTO t1(col1) VALUES('A')
INSERT INTO t1(col1) VALUES('A')
INSERT INTO t1(col1) VALUES('B')
INSERT INTO t1(col1) VALUES('B')
INSERT INTO t1(col1) VALUES('C')
INSERT INTO t1(col1) VALUES('C')
INSERT INTO t1(col1) VALUES('C')
INSERT INTO t1(col1) VALUES('C')
INSERT INTO t1(col1) VALUES('C')
--該解決方案必須兼容SQL Server 2000，所以你不能使用ROW_NUMBER函數(shù)。而且，此方案必須是標(biāo)準(zhǔn)的。
--在這個(gè)解決方案中，將第一次使用一個(gè)非常重要的關(guān)鍵技術(shù)--用數(shù)字輔助表生成副本。以下創(chuàng)建Nums表并用l<=n<=1000000之內(nèi)的1000000個(gè)整數(shù)填充該表。
--第一步是，通過(guò)按col對(duì)行分組來(lái)“壓縮”數(shù)據(jù)，為每個(gè)組返回重復(fù)數(shù)（該組中的行數(shù)），還要用子查詢返回基表中具有最小排序值的行數(shù)。
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1<a.col1)AS smaller FROM [T1]A GROUP BY [col1]
--下一步是擴(kuò)展行數(shù)，即，為每一行創(chuàng)建連續(xù)編號(hào)的副本。
SELECT col1,dups,smaller,n FROM (
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1<a.col1)AS smaller FROM [T1]A GROUP BY [col1]) AS D, Nums
WHERE n<=[dups]
--觀察上表的結(jié)果，理解它是如何產(chǎn)生行號(hào)的。
--行號(hào)可以表示為，具有更小排序值的行數(shù)加上同一排序值組內(nèi)的行號(hào),即 N + smaller。下面列出最終解決方案。
SELECT n+smaller AS rowNum, col1 FROM (
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1<a.col1)AS smaller FROM [T1]A GROUP BY [col1]) AS D, Nums
WHERE n<=[dups]
ORDER BY [rowNum]
--創(chuàng)建一個(gè)填充了100W行數(shù)的Nums表
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
--在生產(chǎn)環(huán)境中也是有用的,例可能常用到的,數(shù)據(jù)分頁(yè).
CREATE PROC usp_GetPage @iRowCount INT ,@iPageNo INT
AS
SELECT * FROM (
SELECT ROW_NUMBER()OVER(ORDER BY productid ASC)RowNum,* FROM production.product)OrderData
WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 AND @iRowCount*@iPageNo
ORDER BY [ProductID] ASC
GO
-- 使用
EXEC usp_getpage 10,20
