SqlServer 2005的排名函数使用小结

尽管从技术上讲,其它排名函数的计算与ROW_NUMBER类似,但它们的的实际应用却少很多。RANK和DENSE——RANK主要用于排名和积分。NTILE更多地用于分析。

先创建一个示例表:

代码如下:

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

(0)

相关推荐

  • SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较

    排名函数是SQL Server2005新加的功能.在SQL Server2005中有如下四个排名函数: 1.row_number 2.rank 3.dense_rank 4.ntile 下面分别介绍一下这四个排名函数的功能及用法.在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示: 图1 其中field1字段的类型是int,field2字段的类型是varchar 一.row_number row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号

  • SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    ROW_NUMBER() 说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始.语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) .备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序.参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区.       <o

  • oracle排名函数的使用方法分享

    在oracle中,有rank,dense_rank,row_number,以及分组排名partition. 说明: rank:排名会出现并列第n名,它之后的会跳过空出的名次,例如:1,2,2,4 dense_rank:排名会出现并列第n名,它之后的名次为n+1,例如:1,2,2,3 row_number:排名采用唯一序号连续值,例如1,2,3,4 partition:将排名限制到某一分组 格式: row_number() over(partition by bb.channel_name ord

  • Oracle排名函数(Rank)实例详解

    --已知:两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数:dense_rank,rank ·查询原始数据:学号,姓名,科目名,成绩 select * from t_score S_ID S_NAME SUB_NAME SCORE 1 张三 语文 80.00 2 李四 数学 80.00 1 张三 数学 0.00 2 李四 语文 50.00 3 张三丰 语文 10.00 3 张三丰 数学 3 张三丰 体育 120.00 4 杨过 JAVA 9

  • 实例讲解sql server排名函数DENSE_RANK的用法

    一.需求 之前sql server 的排名函数用得最多的应该是RoW_NUMBER()了,我通常用ROW_NUMBER() + CTE 来实现分页:今天逛园,看到另一个内置排名函数还不错,自己顺便想了一个需求,大家可以花1分钟先想想要怎么实现. 需求很简单:求成绩排名前五的学生信息. 例如: 由于成绩可以并列,所以前五名可能有多个.例如: 测试数据: declare @t table (ID int, StudentName nvarchar(15), Score int) insert int

  • SqlServer 2005的排名函数使用小结

    尽管从技术上讲,其它排名函数的计算与ROW_NUMBER类似,但它们的的实际应用却少很多.RANK和DENSE--RANK主要用于排名和积分.NTILE更多地用于分析. 先创建一个示例表: 复制代码 代码如下: 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

  • SqlServer 2005 中字符函数的应用

    复制代码 代码如下: USE Demo GO /* 将表Code的列String中的值提取放到Record表中 String 中字符类型为 dsddddd,2222222,222221,3 其中最后一位为标记对于Record表中的BiaoJi 前面的以','分割的是值对应Record表中Value */ GO DROP PROC proc_split_Code GO CREATE PROC proc_split_Code AS BEGIN SET NOCOUNT ON DECLARE @Coun

  • SQLServer 日期函数大全(小结)

    一.统计语句 1.--统计当前[>当天00点以后的数据] SELECT * FROM 表 WHERE CONVERT(Nvarchar, dateandtime, 111) = CONVERT(Nvarchar, GETDATE(), 111) ORDER BY dateandtime DESC 2.--统计本周 SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0 3.--统计本月 SELECT * FROM 表 WHERE da

  • SQLServer RANK() 排名函数的使用

    本文主要介绍了SQLServer RANK() 排名函数的使用,具体如下: -- 例子表数据 SELECT * FROM test; -- 统计分数 SELECT name,SUM(achievement) achievement FROM test GROUP BY name; -- 按统计分数做排行 SELECT RANK() OVER( ORDER BY SUM(achievement) desc) 排行,name,SUM(achievement) achievement FROM tes

  • SQL查询排名函数实例

    在实际开发中经常会遇到计算某个字段的排名的情况 如下表:totak_sales 现在又如此要求:按sales的逆序排序,要求添加一个sales_rank字段,显示排名顺序 方法: 复制代码 代码如下: SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name

  • 模拟SQLSERVER的两个函数:dateadd(),datediff()

    <?php//文件名:date.inc.php3//在使用这两个函数前,要先将日期或日期时间转换成timestamp类型.//如://$today=mktime(0,0,0,date("m"),date("d"),date("Y")); /****模拟sqlserver中的dateadd函数*******$part 类型:string取值范围:year,month,day,hour,min,sec表示:要增加的日期的哪个部分$n 类型:数值

  • php 可变函数使用小结

    可变函数 PHP 支持可变函数的概念.这意味着如果一个变量名后有圆括号,PHP 将寻找与变量的值同名的函数,并且尝试执行它.可变函数可以用来实现包括回调函数,函数表在内的一些用途. 变量函数不能用于语言结构,例如 echo(),print(),unset(),isset(),empty(),include(),require() 以及类似的语句.需要使用自己的包装函数来将这些结构用作变量函数. 先将我的伪代码写上. protected $model; public function __cons

  • 详解SqlServer数据库中Substring函数的用法

    功能:返回字符.二进制.文本或图像表达式的一部分 语法:SUBSTRING ( expression, start, length ) 1.substring(操作的字符串,开始截取的位置,返回的字符个数) 例如: 从'abbccc'中返回'ccc',charindex函数用法(charindex(查找的字符串,被查找的字符串,开始查找的位置),例如查找'abbccc'中第一个'c'出现的位置,charindex('c','abbccc',1)) declare @str1 varchar(25

随机推荐