• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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.qtys1.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.col1a.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.col1a.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.col1a.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
    您可能感兴趣的文章:
    • SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
    • SQL查询排名函数实例
    • 实例讲解sql server排名函数DENSE_RANK的用法
    • sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍
    上一篇:SQLServer 2005数据库连接字符串 连接sql2005必备资料
    下一篇:SqlServer 2005 简单的全文检索
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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