SqlServer 2005的排名函數(shù)使用小結(jié)

SET NOCOUNT ON
USE [tempdb]
IF OBJECT_ID('Sales')IS NOT NULL
DROP TABLE sales

CREATE TABLE Sales
(
empid VARCHAR(10) NOT NULL PRIMARY KEY,
mgrid VARCHAR(10) NOT NULL,
qty INT NOT NULL
)

INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('A','Z',300)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('B','X',100)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('C','X',200)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('D','Y',200)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('E','Z',250)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('F','Z',300)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('G','X',100)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('H','Y',150)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('I','X',250)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('J','Z',100)
INSERT INTO [Sales] (empid,[mgrid],[qty])VALUES('K','Y',200)

CREATE INDEX idx_qty_empid ON [Sales](qty,empid)
CREATE INDEX idx_mgrid_qty_empid ON sales(mgrid,qty,empid)

--
SELECT * FROM [Sales]

--排名函數(shù)
/**/

--Sql Server 2005排名函數(shù)只能用于查詢的SELECT 和 ORDER BY 子句中。排名計算（無論你使用什么方法）的最佳索引是在分區(qū)列、排序列、覆蓋列上創(chuàng)建的索引。
--行號：是指按指定順序為查詢結(jié)果集中的行分配的連續(xù)整數(shù)。在后面的節(jié)中，將描述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
--分區(qū)
SELECT mgrid,empid,qty,ROW_NUMBER()OVER(PARTITION BY mgrid ORDER BY qty,empid)AS RowNum
FROM [Sales]
ORDER BY mgrid,qty
--=====之前2000版本基于集合的方法實現(xiàn)
--唯一排序列：給定一個唯一的分區(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í)行計劃，（順序是從上至下，從右至左看）會發(fā)現(xiàn)有兩個不同的運算符使用了聚集索引。第一個是完整掃描以返回所有的行(這個例子是11行)；第二個運算符先為每個外部執(zhí)行查找，再執(zhí)行局部掃描，以完成統(tǒng)計。還記得嗎？影響數(shù)據(jù)處理查詢性能的主要因素通常中I/O。這種方式在小數(shù)據(jù)量時不明顯,但當(dāng)數(shù)據(jù)量較大時(大于千條),由于每一條記錄都需要將全部表掃描一次,使用這種方法掃描的總行數(shù)將是1+2+3+N，對于整體上100000行的表，你一共會掃描50005000行。順便提一下，計算前N個正整數(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í)行時間:
CPU 時間 = 47 毫秒，占用時間 = 674 毫秒。

(31465 行受影響)

SQL Server 執(zhí)行時間:
CPU 時間 = 133094 毫秒，占用時間 = 134030 毫秒。

可想而知,新排名函數(shù)的憂化方面是很不錯的.
*/

--不唯一排序列和附加屬性：當(dāng)排序列不唯一時，你可以通過引入一個附加屬性使它唯一。以下查詢按qty和empid的順序生成行號
SELECT empid,qty,(SELECT COUNT(*) FROM [Sales] s2 WHERE s2.qtys1.qty OR ((s2.qty=s1.qty AND s2.empid=s1.empid)))AS rowNum
FROM [Sales] s1 ORDER BY qty,empid
--以上示例qty為排序列，empid為附加列。要統(tǒng)計具有相同或更小排序列表值（qty+empid)的行，在子查詢中使用以下表達(dá)式
--inner_qty outer_qty OR (inner_qty=outer_qty AND inner_empid = outer_empid)

--沒有附加屬性的不唯一序列：當(dāng)你要根據(jù)不唯一排序列分配行號，而且不使用附加屬性時，在SQL Server 2005之前的版本中用基于集合的方法解決該問題就更復(fù)雜了。通過以下代碼清單創(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)的。
--在這個解決方案中，將第一次使用一個非常重要的關(guān)鍵技術(shù)--用數(shù)字輔助表生成副本。以下創(chuàng)建Nums表并用l=n=1000000之內(nèi)的1000000個整數(shù)填充該表。
--第一步是，通過按col對行分組來“壓縮”數(shù)據(jù)，為每個組返回重復(fù)數(shù)（該組中的行數(shù)），還要用子查詢返回基表中具有最小排序值的行數(shù)。
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1a.col1)AS smaller FROM [T1]A GROUP BY [col1]
--下一步是擴展行數(shù)，即，為每一行創(chuàng)建連續(xù)編號的副本。
SELECT col1,dups,smaller,n FROM (
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1a.col1)AS smaller FROM [T1]A GROUP BY [col1]) AS D, Nums
WHERE n=[dups]
--觀察上表的結(jié)果，理解它是如何產(chǎn)生行號的。
--行號可以表示為，具有更小排序值的行數(shù)加上同一排序值組內(nèi)的行號,即 N + smaller。下面列出最終解決方案。
SELECT n+smaller AS rowNum, col1 FROM (
SELECT col1,COUNT(*) AS dups,(SELECT COUNT(*) FROM [T1]B WHERE b.col1a.col1)AS smaller FROM [T1]A GROUP BY [col1]) AS D, Nums
WHERE n=[dups]
ORDER BY [rowNum]

--創(chuàng)建一個填充了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ù)分頁.
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