--排名函數
/**/
--Sql Server 2005排名函數只能用于查詢的SELECT 和 ORDER BY 子句中。排名計算（無論你使用什么方法）的最佳索引是在分區列、排序列、覆蓋列上創建的索引。
--行號：是指按指定順序為查詢結果集中的行分配的連續整數。在后面的節中，將描述Sql Server 2005與之前版本中計算行號的工具與方法。
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
--分區
SELECT mgrid,empid,qty,ROW_NUMBER()OVER(PARTITION BY mgrid ORDER BY qty,empid)AS RowNum
FROM [Sales]
ORDER BY mgrid,qty
--=====之前2000版本基于集合的方法實現
--唯一排序列：給定一個唯一的分區 + 排序列組合 (如下例的唯一的分區是empid,排序列empid
SELECT empid,(SELECT COUNT(*) FROM [Sales] AS s2 WHERE s2.empid<=s1.empid)AS rowNum
FROM [Sales] s1 ORDER BY [empid]
--查看執行計劃，（順序是從上至下，從右至左看）會發現有兩個不同的運算符使用了聚集索引。第一個是完整掃描以返回所有的行(這個例子是11行)；第二個運算符先為每個外部執行查找，再執行局部掃描，以完成統計。還記得嗎？影響數據處理查詢性能的主要因素通常中I/O。這種方式在小數據量時不明顯,但當數據量較大時(大于千條),由于每一條記錄都需要將全部表掃描一次,使用這種方法掃描的總行數將是1+2+3+N，對于整體上100000行的表，你一共會掃描50005000行。順便提一下，計算前N個正整數之各的公式是(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]
/* 結果:
(31465 行受影響)
SQL Server 執行時間:
CPU 時間 = 47 毫秒，占用時間 = 674 毫秒。
(31465 行受影響)
SQL Server 執行時間:
CPU 時間 = 133094 毫秒，占用時間 = 134030 毫秒。
可想而知,新排名函數的憂化方面是很不錯的.
*/
--不唯一排序列和附加屬性：當排序列不唯一時，你可以通過引入一個附加屬性使它唯一。以下查詢按qty和empid的順序生成行號
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為附加列。要統計具有相同或更小排序列表值（qty+empid)的行，在子查詢中使用以下表達式
--inner_qty < outer_qty OR (inner_qty=outer_qty AND inner_empid <= outer_empid)
--沒有附加屬性的不唯一序列：當你要根據不唯一排序列分配行號，而且不使用附加屬性時，在SQL Server 2005之前的版本中用基于集合的方法解決該問題就更復雜了。通過以下代碼清單創建并填充該表。
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函數。而且，此方案必須是標準的。
--在這個解決方案中，將第一次使用一個非常重要的關鍵技術--用數字輔助表生成副本。以下創建Nums表并用l<=n<=1000000之內的1000000個整數填充該表。
--第一步是，通過按col對行分組來“壓縮”數據，為每個組返回重復數（該組中的行數），還要用子查詢返回基表中具有最小排序值的行數。
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1<a.col1)AS smaller FROM [T1]A GROUP BY [col1]
--下一步是擴展行數，即，為每一行創建連續編號的副本。
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]
--觀察上表的結果，理解它是如何產生行號的。
--行號可以表示為，具有更小排序值的行數加上同一排序值組內的行號,即 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]
--創建一個填充了100W行數的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
--在生產環境中也是有用的,例可能常用到的,數據分頁.
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
