SqlServer2008实例45排名函数

目录

1.生成递增行号

2.根据排名返回行

3.根据无间隔排名返回行

4.使用NTILE

可以使用排名函数返回和结果集中的每行关联的排名值。

              排名函数

函  数描  述
ROW_NUMBER为结果集中的每行返回递增整数
RANK和ROW_NUMBER相似.RANK为结果集中的每行递增值.主要的区别是如果行有重复值,它们会有相同的排名值
DENSE_RANKDENSE_RANK和RANK几乎一样,只是如果行有重复值,DENSE RANK不在排名值中返回间隔
NTILENTILE根据排序和可选的分区子句把结果集划分成指定数量的分组

1.生成递增行号

    ROW_NUMBER为结果集中的每行返回递增的整数。ROW_NUMBER的语法如下:
   ROW_NUMBER()  OVER ( [<partition_by_clause>]<order_by_clause>)
    第一个可选参数partition_by_clause为分区列中的每次修改重置行号。第二个参数order_by_clause指定ROW_NUMBER应用到结果集的次序。

   示例1:返回结果集中间的6行,根据名字排序:

USE AdventureWorks
GO

--选择结果集中章从255到260的行
SELECT p.ProductID,P.Name,p.RowNumber
FROM 
    (
     SELECT ProductID,
            Name,
            ROW_NUMBER() OVER(ORDER BY Name) RowNumber
     FROM Production.Product
     ) p
WHERE p.RowNumber BETWEEN 255 AND 260  

    可选的partition_by_clause允许为分区列的每次修改重置行号。

   示例2:结果根据Shelf来分区并且根据ProductlD排序:

USE AdventureWorks
GO

SELECT Shelf,
       ProductID,
       ROW_NUMBER () OVER
       (PARTITION BY Shelf ORDER BY ProductID) RowNumber
FROM Production.ProductInventory 

2.根据排名返回行

    在这个技巧中,会演示使用RANK函数来应用基于SalesQuota值的排名值。RANK返回行在结果集中的排名(如果指定了可选的分区子句,就是行在结果集的分区中的排名)。RANK的语法如下:
    RANK() OVER( [<partition_by_clause>] <order_by_clause>)
    主要的区别是如果行有相同的值存在,那么他们会获取相同的排名值,

示例1:

USE AdventureWorks
GO

SELECT SalesPersonID,
       QuotaDate,
       Salesquota,
       RANK() OVER (ORDER BY SalesQuota DESC) as RANK
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00

    和ROW NUMBER一样,OVER子句包含可选的partition_by_clause和必需的order_by_clause。order_by_clause决定RANK值应用到每行的顺序,可选的partition_by_clause用于进一步划分排序分组,

示例2:

USE AdventureWorks
GO

SELECT h.SalesPersonID,
	   s.TerritoryID,
	   h.QuotaDate,
	   h.SalesQuota,
	   RANK() OVER (PARTITION BY s.TerritoryID ORDER BY h.SalesQuota DESC) as RANK
FROM Sales.SalesPersonQuotaHistory h
INNER JOIN Sales.SalesPerson s ON
      h.SalesPersonID=s.SalesPersonID
WHERE s.TerritoryID IN(5,6,7)   

3.根据无间隔排名返回行

示例:

USE AdventureWorks
GO

SELECT SalesPersonID,
       SalesQuota,
       DENSE_RANK() OVER (ORDER BY SalesQuota DESC) as BENSE_RANK
FROM Sales.SalesPersonQuotaHistory
WHERE salesQuota BETWEEN 266000.00 AND 319000.00

4.使用NTILE

    NTILE根据排序和可选的分区把结果集分成指定数量的分组。语法和其他排名函数相似,只是它包含一个integer_expression:
    NTILE(integer_expresslon) OVER([<partitlon_by_clause>] <order_by_clause>)
    Integer_expression用于指定要将结果划分成的分组数。

   示例:对Sales .SalePersonQuotaHistory表操作的NTILE排名函数:

USE AdventureWorks
GO

SELECT SalesPersonID,
       SalesQuota,
       NTILE(4) OVER (ORDER BY SalesQuota DESC) as NTILE
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota BETWEEN 266000.00 AND 319000.00

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页