SqlServer 2005的排名函数使用小结

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]

–排名函数
/**/

–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