A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© 吴步兵 中级黑马   /  2012-11-9 22:46  /  1402 人查看  /  1 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

本帖最后由 吴步兵 于 2012-11-9 22:56 编辑

这些函数被用于以与结果集顺序无关的特定顺序,枚举已排序的或排在前面的结果集。
ROW_NUMBER()函数
ROW_NUMBER()函数根据作为参数传递给这个函数的ORDER BY子句的值,返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配,返回值将是递增的,以升序排列。如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同,这些值将不会按顺序列出,但它们表示ROW_NUMBER函数的ORDER BY子句的顺序。如下面的例子和结果所示
  1. SELECT ProductCategoryID
  2. ,Name
  3. ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
  4. FROM Production.ProductCategory
  5. ORDER BY Name

复制代码
由于ROW_NUMBER()调用中的ORDERBY子句和查询结果的顺序匹配,所以对这些结果按顺序列出,如下图所示:
不过,在函数调用中使用另一个ORDER BY子句时,这些值就是无序的了。
  1. SELECT ProductCategoryID
  2. ,Name
  3. ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
  4. FROM Production.ProductCategory
  5. ORDER BY ProductCategoryID
复制代码
这是了解如何使用ORDER BY子句对结果进行排序的有效方法。如下图所示:
RANK()DENSE_RANK()函数
这两个函数与ROW_NUMBER()函数类似,因为它们都返回一个基于ORDER BY子句的值。不过这些值不一定永远是唯一的。排列值对于所提供的ORDER BY子句中的重复结果而言也是重复的,而且唯一性是仅仅基于ORDER BY列表中的唯一值的。这些函数用不同的方法来处理重复的值。RANK()函数保留列表中行的位置序号,对于每个重复的值,该函数会跳过下面与其相邻的值,于是就可以将下一个不重复的值保留在正确的位置上。
其行为类似于短跑比赛中的并列成绩。例如刘翔与Dayron Robles(古巴)在110栏的比赛中都跑出了12’92的成绩,那他们就是并列第一,而其后的一名选手将会获得第三名的成绩。
  1. SELECT ProductID
  2. ,Name
  3. ,ListPrice
  4. ,RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]
  5. FROM Production.Product
  6. ORDER BY [Rank]
复制代码
注意在下图的结果列表中,重复的价格值所对应的结果是相同的,而每个连接之后的值都被跳过了。比如,产品"Road-150 Red, 52"和"Road-150 Red, 56"都排在第1,而接下来的行"Mountain-100 Silver,38"就排在第6了。
DENSE_RANK()函数的工作方式与RANK()函数相同,不过它不会跳过每个连接后的值,这样就不会有值被跳过了,但是在连接处排列序号位置将会丢失。
  1. SELECT ProductID
  2. ,Name
  3. ,ListPrice
  4. ,DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]
  5. FROM Production.Product
  6. ORDER BY [Rank]
复制代码
下图的结果重复了排列值,但是不会跳过列中的任何数字。
NTILE(n)函数
这个函数也用于对结果进行排列,并返回一个整型的排列值,但是它不会对结果以唯一的排列顺序进行枚举,而是将结果切分为有限数量的排列组。比如,一个表有10 000行,使用1000为参数值调用NTILE()函数,即NTILE(1000),并将结果分成以10为单位的1000个组,每个组赋予相同的排列值。和本节讨论的其他排列函数一样,NTILE()函数也支持OVER(ORDER BY…)语法。下面的例子根据产品价格,按照从高到低的顺序把Product表分为50组产品:
  1. SELECT ProductID
  2. ,Name
  3. ,ListPrice
  4. ,NTILE(50) OVER (ORDER BY ListPrice DESC) AS GroupedProducts
  5. FROM Production.Product
  6. ORDER BY GroupedProducts
复制代码
结果为:

评分

参与人数 1技术分 +1 收起 理由
张文 + 1

查看全部评分

1 个回复

倒序浏览
值得学习ing!
回复 使用道具 举报
您需要登录后才可以回帖 登录 | 加入黑马