温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(6004)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
loftPermalink:'',
id:'fks_087066',
blogTitle:'应用RANK函数排名成绩',
blogAbstract:'
利用RANK函数同样可以达到制作排名的目的。RANK函数返回一个数字在数字列表中的排位，数字的排位是其大小与列表中其他值的比值。
使用RANK函数制作排名的具体操作步骤如下。
（1）选择单元格K3，如图3-88所示。
（2）输入“=RANK(H3,$H$3:$H$32,0)”，如图3-89所示。
（3）完成后按Enter键，得到第一个学生的总分排名，如图3-90所示。
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:8,
publishTime:2,
permalink:'blog/static/',
commentCount:1,
mainCommentCount:1,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'',
hmcon:'0',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}博客访问： 7068877
博文数量： 560
注册时间：
认证徽章：
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Linux
数据库系统应用，最典型的应用场景就是各种报表生成。作为开发人员，最理想的情况是“一句SQL解决一张报表”。但是，面对需求的“云谲波诡”，我们常常会“绞尽脑汁”。这个时候，丰富的经验和知识积累往往是我们解决问题的关键。
在Oracle自拓展SQL功能中，分析函数（Analytical Function）是非常强大的工具。区别于传统SQL函数，分析函数具有功能强大、拓展性强和使用方便的特点。实践中，一些使用标准SQL很难甚至不可能实现的需求，我们借助分析函数就可以“一招定乾坤”。
本篇我们介绍几个Oracle典型的排序分析函数，来帮助我们解决实际问题。
1、从rownum谈起
我们还是选择Oracle 11gR2进行测试。
SQL> select * from v$
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE&&&&&&& 11.2.0.1.0&&&&&&&& Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
数据基础表emp，如下。
SQL> select empno, ename, sal, hiredate,
EMPNO ENAME&&&&&&&&&&& SAL HIREDATE&&& DEPTNO
----- ---------- --------- ----------- ------
&7369 SMITH&&&&&&&& 800.00 17-十二月-8&&&& 20
&7499 ALLEN&&&&&&& -二月-81&&&&& 30
&7521 WARD&&&&&&&& -二月-81&&&&& 30
&（篇幅原因，有省略……）
&7934 MILLER&&&&&& -一月-82&&&&& 10
14 rows selected
我们排序的时候，经常会使用到rownum。一种常用的思路，是先用order by排列好，之后用rownum标号作为排序。但是，rownum往往不会像我们希望的如此工作。
SQL> select empno, ename, sal, deptno, rownum f
EMPNO ENAME&&&&&&&&&&& SAL DEPTNO& &&&ROWNUM
----- ---------- --------- ------ ----------
&7369 SMITH&&&&&&&& 800.00&&&& 20&&&&&&&&& 1
&7900 JAMES&&&&&&&& 950.00&&&& 30&&&&&&&& 12
&7876 ADAMS&&&&&&& 1100.00&&&& 20&&&&&&&& 11
&7521 WARD&&&&&&&& 1250.00&&&& 30&&&&&&&&& 3
&7654 MARTIN&&&&&& 1250.00&&&& 30&&&&&&&&& 5
&7934 MILLER&&&&&& 1300.00&&&& 10&&&&&&&& 14
&7844 TURNER&&&&&& 1500.00&&&& 30&&&&&&&& 10
&7499 ALLEN&&&&&&& 1600.00&&&& 30&&&&&&&&& 2
&7782 CLARK&&&&&&& 2450.00&&&& 10&&&&&&&&& 7
&7698 BLAKE&&&&&&& 2850.00&&&& 30&&&&&&&&& 6
&7566 JONES&&&&&&& 2975.00&&&& 20&&&&&&&&& 4
&7788 SCOTT&&&&&&& 3000.00&&&& 20&&&&&&&&& 8
&7902 FORD&&&&&&&& 3000.00&&&& 20&&&&&&&& 13
&7839 KING&&&&&&&& 5000.00&&&& 10&&&&&&&&& 9
14 rows selected
最后的数据集合，的确是按照我们希望的sal排序动作结果。但是rownum并没有按照我们希望的出现排序“序号”作用。
这个问题的根源是Oracle Rownum的机理。Rownum并不是一个真实存在的数据列，而是一个随数据集生成而生成的数据列。从上面的结果看，应该是Oracle首先生成了rownum数据列，之后再按照sal进行排序。所以，rownum并不能像我们想象的那样处理。
一些方法可以使用在这个问题上，主要是嵌套子查询方法，让我们可以使用ronwum来解决这个问题。
SQL> select t.*,rownum from (select empno, ename, sal, deptno from emp order by sal)
EMPNO ENAME&&&&&&&&&&& SAL DEPTNO&&&& ROWNUM
----- ---------- --------- ------ ----------
&7369 SMITH&&&&&&&& 800.00&&&& 20&&&&&&&&& 1
&7900 JAMES&&&&&&&& 950.00&&&& 30&&&&&&&&& 2
&7876 ADAMS&&&&&&& 1100.00&&&& 20&&&&&&&&& 3
&7521 WARD&&&&&&&& 1250.00&&&& 30&&&&&&&&& 4
&7654 MARTIN&&&&&& 1250.00&&&& 30&&&&&&&&& 5
&7934 MILLER&&&&&& 1300.00&&&& 10&&&&&&&&& 6
&7844 TURNER&&&&&& 1500.00&&&& 30&&&&&&&&& 7
&7499 ALLEN&&&&&&& 1600.00&&&& 30&&&&&&&&& 8
&7782 CLARK&&&&&&& 2450.00&&&& 10&&&&&&&&& 9
&7698 BLAKE&&&&&&& 2850.00&&&& 30&&&&&&&& 10
&7566 JONES&&&&&&& 2975.00&&&& 20&&&&&&&& 11
&7788 SCOTT&&&&&&& 3000.00&&&& 20&&&&&&&& 12
&7902 FORD&&&&&&&& 3000.00&&&& 20&&&&&&&& 13
&7839 KING&&&&&& &&5000.00&&&& 10&&&&&&&& 14
14 rows selected
结果正确，不过这显然不是什么好方法。在官方手段中，Oracle推荐使用分析函数来解决序号问题。根据不同的实际需求，可以使用row_number、rank和dense_rank几个选择。
2、row_number()
Row_number是一个单纯的序号生成器。我们需要遵从分析函数的具体规则，告诉row_number函数按照那个数据列进行排序和生成行号即可。
SQL> select empno, ename, sal, deptno, row_number() over (order by sal)
EMPNO ENAME&&&&&&&&&&& SAL DEPTNO ROW_NUMBER()OVER(ORDERBYSAL)
----- ---------- --------- ------ ----------------------------
&7369 SMITH&&&&&&&& 800.00&&&& 20&&&&&&&&&&&&&&&&&&&&&&&&&&& 1
&7900 JAMES&&&&&&&& 950.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&&& 2
&7876 ADAMS&&&&&&& 1100.00&&&& 20&&&&&&&&&&&&&&&&&&&&&&&&&&& 3
&7521 WARD&&&&&&&& 1250.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&&& 4
&7654 MARTIN&&&&&& 1250.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&&& 5
&7934 MILLER&&&&&& 1300.00&&&& 10&&&&&&&&&&&&&&&&&&&&&&&&&&& 6
&7844 TURNER&&&&&& 1500.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&&& 7
&7499 ALLEN&&&&&&& 1600.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&&& 8
&7782 CLARK&&&&&&& 2450.00&&&& 10&&&&&&&&&&&&&&&&&&&&&&&&&&& 9
&7698 BLAKE&&&&&&& 2850.00&&&& 30&&&&&&&&&&&&&&&&&&&&&&&&&& 10
&7566 JONES&&&&&&& 2975.00&&&& 20&&&&&&&&&&&&&&&&&&&&&&&&&& 11
&7788 SCOTT&&&&&&& 3000.00&&&& 20&&&&&&&&&&&&&&&&&&&&&&&&&& 12
&7902 FORD&&&&&&&& 3000.00&&&& 20&&&&&&&&&&&&&&&&&&&&&&&&&& 13
&7839 KING&&&& &&&&5000.00&&&& 10&&&&&&&&&&&&&&&&&&&&&&&&&& 14
14 rows selected
正是我们期望的结果。我们注意一下row_number的函数用法，在over后面的括号中，书写上排序原则和方法。从执行计划上，row_number带有很典型的分析函数特性，是一个window标记操作。
SQL> explain plan for select empno, ename, sal, deptno, row_number() over (order by sal)
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------
| Id& | Operation&&&&&&&&& | Name | Rows& | Bytes | Cost (%CPU)| Time&&&& |
---------------------------------------------------------------------------
|&& 0 | SELECT STATEMENT&& |&&&&& |&&& 14 |&& 238 |&&&& 4& (25)| 00:00:01 |
|&& 1 | &WINDOW SORT&&&&&& |&&&&& |&&& 14 |&& 238 |&&&& 4& (25)| 00:00:01 |
|&& 2 |&& TABLE ACCESS FULL| EMP& |&&& 14 |&& 238 |&&&& 3&& (0)| 00:00:01 |
---------------------------------------------------------------------------
9 rows selected
分析函数最大的一个功能是可以使用partition可选参数，用来在其中分组。这个是普通函数很难实现的。例如：我们希望按照部门进行薪水排序，显示出每个员工在部门内部的薪水排名。
SQL> select empno, ename, sal, deptno, row_number() over (partition by deptno order by sal desc) sal_
EMPNO ENAME&&&&&&&&&&& SAL DEPTNO&& SAL_RANK
----- ---------- --------- ------ ----------
&7839 KING&&&&&&&& 5000.00&&&& 10&&&&&&&&& 1
&7782 CLARK&&&&&&& 2450.00&&&& 10&&&&&&&&& 2
&7934 MILLER&&&&&& 1300.00&&&& 10&&&&&&&&& 3
&7788 SCOTT&&&&&&& 3000.00&&&& 20&&&&&&&&& 1
&7902 FORD&&&&&&&& 3000.00&&&& 20&&&&&&&&& 2
&7566 JONES&&&&&&& 2975.00&&&& 20&&&&&&&&& 3
&7876 ADAMS&&&&&&& 1100.00&&&& 20&&&&&&&&& 4
&7369 SMITH&&&&&&&& 800.00&&&& 20&&&&&&&&& 5
&7698 BLAKE&&&&&&& 2850.00&&&& 30&&&&&&&&& 1
&7499 ALLEN&&&&&&& 1600.00&&&& 30&&&&&&&&& 2
&7844 TURNER&&&&&& 1500.00&&&& 30&&&&&&&&& 3
&7654 MARTIN&&&&&& 1250.00&&&& 30&&&&&&&&& 4
&7521 WARD&&&&&&&& 1250.00&&&& 30&&&&&&&&& 5
&7900 JAMES&&&&&&&& 950.00&&&& 30&&&&&&&&& 6
14 rows selected
注意，row_number中的排序参数是不能少的！
SQL> select empno, ename, sal, deptno, row_number()
select empno, ename, sal, deptno, row_number() from emp
ORA-30484: 丢失的此函数窗口说明
SQL> select empno, ename, sal, deptno, row_number() over ()
select empno, ename, sal, deptno, row_number() over () from emp
ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
排序操作一个有争议和差异的需求点，就是当有相同取值的时候，排序序号的差异。从row_number行为看，Oracle给相同sal的进行顺序排下去的。Oracle还提供了rank和dense_rank功能。
分析函数排序的好处之一就是可以不使用order by的占位，我们可以在一个SQL中，生成多个数据列排序序号。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, row_number() over (order by hiredate) hir_row fro
EMPNO ENAME&&&&&&&&&&& SAL&&& SAL_ROW&&& HIR_ROW
----- ---------- --------- ---------- ----------
&7369 SMITH&&&&&&&& 800.00&&&&&&&&& 1&&&&&&&&& 1
&7499 ALLEN&&&&&&& 1600.00&&&&&&&&& 8&&&&&&&&& 2
&7521 WARD&&&&&&&& 1250.00&&&&&&&&& 4&&&&&&&&& 3
&7566 JONES&&&&&&& 2975.00&&&&&&&& 11&&&&&&&&& 4
&7654 MARTIN&&&&&& 1250.00&&&&&&&&& 5&&&&&&&&& 8
&7698 BLAKE&&&&&&& 2850.00&&&&&&&& 10&&&&&&&&& 5
&7782 CLARK&&&&&&& 2450.00&&&&&&&&& 9&&&&&&&&& 6
&7788 SCOTT&&&&&&& 3000.00&&&&&&&& 12&&&&&&&& 13
&7839 KING&&&&&&&& 5000.00&&&&&&&& 14&&&&&&&&& 9
&7844 TURNER&&&&&& 1500.00&&&&&&&&& 7&&&&&&&&& 7
&7876 ADAMS&&&&&&& 1100.00&&&&&&&&& 3&&&&&&&& 14
&7900 JAMES&&&&&&&& 950.00&&&&&&&&& 2&&&&&&&& 10
&7902 FORD&&&&&&&& 3000.00&&&&&&&& 13&&&&&&&& 11
&7934 MILLER&&&&&& 1300.00&&&&&&&&& 6&&&&&&&& 12
14 rows selected
3、rank函数
Rank是和row_number相似行为的分析函数。在用法上两者是没有显著性区别的，按照官方说法：rank会跨过tie的情况，也就是重值情况。我们看一下函数结果。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, rank() over (order by sal) sal_rank
EMPNO ENAME&&&&&&&&&&& SAL&&& SAL_ROW&& SAL_RANK
----- ---------- --------- ---------- ----------
&7369 SMITH&&&&&&&& 800.00&&&&&&&&& 1&&&&&&&&& 1
&7900 JAMES&&&&&&& &950.00&&&&&&&&& 2&&&&&&&&& 2
&7876 ADAMS&&&&&&& 1100.00&&&&&&&&& 3&&&&&&&&& 3
&7521 WARD&&&&&&&& 1250.00&&&&&&&&& 4&&&&&&&&& 4
&7654 MARTIN&&&&&& 1250.00&&&&&&&&& 5&&&&&&&&& 4
&7934 MILLER&&&&&& 1300.00&&&&&&&&& 6&&&&&&&&& 6
&7844 TURNER&&&&&& 1500.00&&& &&&&&&7&&&&&&&&& 7
&7499 ALLEN&&&&&&& 1600.00&&&&&&&&& 8&&&&&&&&& 8
&7782 CLARK&&&&&&& 2450.00&&&&&&&&& 9&&&&&&&&& 9
&7698 BLAKE&&&&&&& 2850.00&&&&&&&& 10&&&&&&&& 10
&7566 JONES&&&&&&& 2975.00&&&&&&&& 11&&&&&&&& 11
&7788 SCOTT&&&&&&& 3000.00&&&&&&&& 12&&&&&&&& 12
&7902 FORD&&&&&&&& 3000.00&&&&&&&& 13&&&&&&&& 12
&7839 KING&&&&&&&& 5000.00&&&&&&&& 14&&&&&&&& 14
14 rows selected
在SQL中我们使用了row_number和rank行为的对比。我们发现在相同的排序取值的情况下，两个SQL函数的结果有差异。Row_number是将排序序号继续下去，内部随机结果。而rank是也将序号继续下去，但是相同取值的时候，相同值占相同的排名。
同样，rank也可以支持partition字句。
SQL> select empno, ename, deptno,sal, rank() over (partition by deptno order by sal) sal_
EMPNO ENAME&&&&& DEPTNO&&&&&& SAL&& SAL_RANK
----- ---------- ------ --------- ----------
&7934 MILLER&&&&&&&& 10&& 1300.00&&&&&&&&& 1
&7782 CLARK&&&&&&&&& 10&& 2450.00&&&&&&&&& 2
&7839 KING&&&&&&&&&& 10&& 5000.00&&&&&&&&& 3
&7369 SMITH&&&&&&&&& 20&&& 800.00&&&&&&&&& 1
&7876 ADAMS&&&&&&&&& 20&& 1100.00&&&&&&&&& 2
&7566 JONES&&&&&&&&& 20&& 2975.00&&&&&&&&& 3
&7788 SCOTT&&&&&&&&& 20&& 3000.00&&&&&&&&& 4
&7902 FORD&&&&&&&&&& 20&& 3000.00&&&&&&&&& 4
&7900 JAMES&&&&&&&&& 30&&& 950.00&&&&&&&&& 1
&7654 MARTIN&&&&&&&& 30&& 1250.00&&&&&&&&& 2
&7521 WARD&&&&&&&&&& 30&& 1250.00&&&&&&&&& 2
&7844 TURNER&&&&&&&& 30&& 1500.00&&&&&&&&& 4
&7499 ALLEN&&&&&&&&& 30&& 1600.00&&&&&&&&& 5
&7698 BLAKE&&&&&&&&& 30&& 2850.00&&&&&&&&& 6
14 rows selected
4、dense_rank函数
Dense_rank和rank的行为类似，下面SQL用于对比效果。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, rank() over (order by sal) sal_rank, dense_rank() over (order by sal) sal_dense_rank
EMPNO ENAME&&&&&&&&&&& SAL&&& SAL_ROW&& SAL_RANK SAL_DENSE_RANK
----- ---------- --------- ---------- ---------- --------------
&7369 SMITH&&&&&&&& 800.00&&&&&&&&& 1&&&&&&&&& 1&&&&&&&&&&&&& 1
&7900 JAMES&&&&&&&& 950.00&&&&&&&&& 2&&&&&&&&& 2&&&&&&&&&&&&& 2
&7876 ADAMS&&&&&&& 1100.00&&&&&&&&& 3&&&&&&&&& 3&&&&&&&&&&&&& 3
&7521 WARD&&&&&&&& 1250.00&&&&&&&&& 4&&&&&&&&& 4&&&&&&&&&&&&& 4
&7654 MARTIN&&&&&& 1250.00&&&&&&&&& 5&&&&&&&&& 4&&&&&&&&&&&&& 4
&7934 MILLER&&&&&& 1300.00&&&&&&&&& 6&&&&&&&&& 6&&&&&&&&&&&&& 5
&7844 TURNER&&&&&& 1500.00&&&&&&&&& 7&&&&&&&&& 7&&&&&&&&&&&&& 6
&7499 ALLEN&&&&&&& 1600.00&&&&&&&&& 8&&&&&&&&& 8&&&&&&&&&&&&& 7
&7782 CLARK&&&&&&& 2450.00&&&&&&&&& 9&&&&&&&&& 9&&&&&&&&&&&&& 8
&7698 BLAKE&&&&&&& 2850.00&&&&&&&& 10&&&&&&&& 10&&&&&&&&&&&&& 9
&7566 JONES&&&&&&& 2975.00&&&&&&&& 11&&&&&&&& 11&&&&&&&&&&&& 10
&7788 SCOTT&&&&&&& 3000.00&&&&&&&& 12&&&&&&&& 12&&&&&&&&&&&& 11
&7902 FORD&&&&&&&& 3000.00&&&&&&&& 13&&&&&&&& 12&&&&&&&&&&&& 11
&7839 KING&&&&&&&& 5000.00&&&&&&&& 14&&&&&&&& 14&&&&&&&&&&&& 12
14 rows selected
Rank和dense_rank相同，在相同的取值情况下，排序序号相同。差异在于后面的序号处理差异。Rank是把编号跳过去，而dense_rank这不跳号。
Oracle分析函数的功能非常强大，很多高级报表SQL都是可以借助这类函数进行编写。
阅读(9465) | 评论(0) | 转发(5) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
字体：[ ] 类型：转载 时间：
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数row_number、rank、dense_rank和ntile，需要的朋友可以参考下。
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数：
　　1.row_number
　　2.rank
　　3.dense_rank
　　4.ntile　　
　　下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表，表结构与表中的数据如图1所示：
　　其中field1字段的类型是int，field2字段的类型是varchar
　　一、row_number
　　row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示：
select row_number() over(order by field1) as row_number,* fromt_table
　　上面的SQL语句的查询结果如图2所示。
　　其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序，然后才能生成序号。
　　实际上，row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序，然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系，这两处的order by 可以完全不同，如下面的SQL语句所示
select row_number() over(order by field2 desc) as row_number,*from t_table order by field1 desc
　　上面的SQL语句的查询结果如图3所示。
　　我们可以使用row_number函数来实现查询表中指定范围的记录，一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录：
with t_rowtableas(　　select row_number() over(order by field1) as row_number,*from t_table)select * from t_rowtable where row_number&1 and row_number&4 order by field1
　　上面的SQL语句的查询结果如图4所示。
　　上面的SQL语句使用了CTE，关于CTE的介绍将读者参阅《SQL Server2005杂谈（1）：使用公用表表达式（CTE）简化嵌套SQL》。
　　另外要注意的是，如果将row_number函数用于分页处理，over子句中的order by 与排序记录的order by 应相同，否则生成的序号可能不是有续的。
　　当然，不使用row_number函数也可以实现查询指定范围的记录，就是比较麻烦。一般的方法是使用颠倒Top来实现，例如，查询t_table表中第2条和第3条记录，可以先查出前3条记录，然后将查询出来的这三条记录按倒序排序，再取前2条记录，最后再将查出来的这2条记录再按倒序排序，就是最终结果。SQL语句如下：
select * from(select top2 * from(select top3 * from t_table order by field1)a order by field1 desc) b order by field1
　　上面的SQL语句查询出来的结果如图5所示。
　　这个查询结果除了没有序号列row_number，其他的与图4所示的查询结果完全一样。
　　二、rank
　　rank函数考虑到了over子句中排序字段值相同的情况，为了更容易说明问题，在t_table表中再加一条记录，如图6所示。
　　在图6所示的记录中后三条记录的field1字段值是相同的。如果使用rank函数来生成序号，这3条记录的序号是相同的，而第4条记录会根据当前的记录数生成序号，后面的记录依此类推，也就是说，在这个例子中，第4条记录的序号是4，而不是2。rank函数的使用方法与row_number函数完全相同，SQL语句如下：
select rank() over(order by field1),* from t_table order by field1
　　上面的SQL语句的查询结果如图7所示。
　　三、dense_rank
　　dense_rank函数的功能与rank函数类似，只是在生成序号时是连续的，而rank函数生成的序号有可能不连续。如上面的例子中如果使用dense_rank函数，第4条记录的序号应该是2，而不是4。如下面的SQL语句所示：
select dense_rank() over(order by field1),* from t_table order by field1
　　上面的SQL语句的查询结果如图8所示。
　　读者可以比较图7和图8所示的查询结果有什么不同
　　四、ntile
　　ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。下面的SQL语句使用ntile函数对t_table表进行了装桶处理：
select ntile(4) over(order by field1)as bucket,* from t_table
　　上面的SQL语句的查询结果如图9所示。
　　由于t_table表的记录总数是6，而上面的SQL语句中的ntile函数指定了桶数为4。
　　也许有的读者会问这么一个问题，SQL Server2005怎么来决定某一桶应该放多少记录呢？可能t_table表中的记录数有些少，那么我们假设t_table表中有59条记录，而桶数是5，那么每一桶应放多少记录呢？
　　实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录，这两个约定如下：
　　1.编号小的桶放的记录不能小于编号大的桶。也就是说，第1捅中的记录数只能大于等于第2桶及以后的各桶中的记录。
　2.所有桶中的记录要么都相同，要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说，如果有个桶，前三桶的记录数都是10，而第4捅的记录数是6，那么第5桶和第6桶的记录数也必须是6。
　　根据上面的两个约定，可以得出如下的算法：
　　//mod表示取余，div表示取整　　if(记录总数mod桶数==0)　　{　　　　recordCount=记录总数div桶数;　　　　将每桶的记录数都设为recordCount　　}　　else　　{　　　　recordCount1=记录总数div桶数+1;　　　　intn=1;　//　n表示桶中记录数为recordCount1的最大桶数　　　　m=recordCount1*n;　　　　while(((记录总数-m)　mod　(桶数-　n))　!=0)　　　　{　　　　　　n++;　　　　　　m=recordCount1*n;　　　　}　　　　recordCount2=(记录总数-m)div　(桶数-n);　　　　将前n个桶的记录数设为recordCount1　　　　将n+1个至后面所有桶的记录数设为recordCount2　　}
　　根据上面的算法，如果记录总数为59，桶数为5，则前4个桶的记录数都是12，最后一个桶的记录数是11。
　　如果记录总数为53，桶数为5，则前3个桶的记录数为11，后2个桶的记录数为10。
　　就拿本例来说，记录总数为6，桶数为4，则会算出recordCount1的值为2，在结束while循环后，会算出recordCount2的值是1，因此，前2个桶的记录是2，后2个桶的记录是1。
&&&&& ROW_NUMBER、RANK、DENSE_RANK 和 NTILE，这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。您可能发现这些新函数有用的典型方案包括：将连续整数分配给结果行，以便进行表示、分页、计分和绘制直方图。
Speaker Statistics 方案
下面的 Speaker Statistics 方案将用来讨论和演示不同的函数和它们的子句。大型计算会议包括三个议题：数据库、开发和系统管理。十一位演讲者在会议中发表演讲，并且为他们的讲话获得 范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：
CREATE TABLE SpeakerStats( &&& speaker&&&&&&& VARCHAR(10) NOT NULL PRIMARY KEY&&& , track&&&&&&&&& VARCHAR(10) NOT NULL&&& , score&&&&&&&&& INT&&&&&&&& NOT NULL&&& , pctfilledevals INT&&&&&&&& NOT NULL&&& , numsessions&&& INT&&&&&&&& NOT NULL)SET NOCOUNT ONINSERT INTO SpeakerStats VALUES('Dan',&&&& 'Sys', 3, 22, 4)INSERT INTO SpeakerStats VALUES('Ron',&&&& 'Dev', 9, 30, 3)INSERT INTO SpeakerStats VALUES('Kathy',&& 'Sys', 8, 27, 2)INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)INSERT INTO SpeakerStats VALUES('Joe',&&&& 'Dev', 6, 20, 2)INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)INSERT INTO SpeakerStats VALUES('Mike',&&& 'DB', 8, 20, 3)INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)INSERT INTO SpeakerStats VALUES('Brian',&& 'Sys', 7, 22, 3)INSERT INTO SpeakerStats VALUES('Kevin',&& 'DB', 7, 25, 4)
每个演讲者都在该表中具有一个行，其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。
ROW_NUMBER
ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。例如，假设您要返回所有演讲者的 speaker、track 和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,&& speaker, track, scoreFROM SpeakerStatsORDER BY score DESC以下为结果集：
rownum speaker&&& track&&&&& score------ ---------- ---------- -----------1&&&&& Jessica&&& Dev&&&&&&& 92&&&&& Ron&&&&&&& Dev&&&&&&& 93&&&&& Suzanne&&& DB&&&&&&&& 94&&&&& Kathy&&&&& Sys&&&&&&& 85&&&&& Michele&&& Sys&&&&&&& 86&&&&& Mike&&&&&& DB&&&&&&&& 87&&&&& Kevin&&&&& DB&&&&&&&& 78&&&&& Brian&&&&& Sys&&&&&&& 79&&&&& Joe&&&&&&& Dev&&&&&&& 610&&&& Robert&&&& Dev&&&&&&& 611&&&& Dan&&&&&&& Sys&&&&&&& 3得 分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的 演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL Server 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。
如 果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的，因此结果是确定的：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC,&&&&&&&&&&&&&&&&&&&&&&&&&& numsessions DESC, speaker) AS rownum,&& speaker, track, score, pctfilledevals, numsessionsFROM SpeakerStatsORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker以下为结果集：
rownum speaker&&& track&&&&& score&&&&&& pctfilledevals numsessions------ ---------- ---------- ----------- -------------- -----------1&&&&& Ron&&&&&&& Dev&&&&&&& 9&&&&&&&&&& 30&&&&&&&&&&&& 32&&&&& Suzanne&&& DB&&&&&&&& 9&&&&&&&&&& 30&&&&&&&&&&&& 33&&&&& Jessica&&& Dev&&&&&&& 9&&&&&&&&&& 19&&&&&&&&&&&& 14&&&&& Michele&&& Sys&&&&&&& 8&&&&&&&&&& 31&&&&&&&&&&&& 45&&&&& Kathy&&&&& Sys&&&&&&& 8&&&&&&&&&& 27&&&&&&&&&&&& 26&&&&& Mike&&&&&& DB&&&&&&&& 8&&&&&&&&&& 20&&&&&&&&&&&& 37&&&&& Kevin&&&&& DB&&&&&&&& 7&&&&&&&&&& 25&&&&&&&&&&&& 48&&&&& Brian&&&&& Sys&&&&&&& 7&&&&&&&&&& 22&&&&&&&&&&&& 39&&&&& Robert&&&& Dev&&&&&&& 6&&&&&&&&&& 28&&&&&&&&&&&& 210&&&& Joe&&&&&&& Dev&&&&&&& 6&&&&&&&&&& 20&&&&&&&&&&&& 211&&&& Dan&&&&&&& Sys&&&&&&& 3&&&&&&&&&& 22&&&&&&&&&&&& 4新的排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。
另一个好处是语法的简单性。为了让您感受一下通过使用在 SQL Server 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效，请考虑下面的 SQL Server 2000 查询，它返回与上一个查询相同的结果：SELECT (SELECT COUNT(*)&& FROM SpeakerStats AS S2&& &&& &&& &&& WHERE S2.score & S1.score&&&& &&& &&& &&& OR (S2.score = S1.score&&&&&&&& AND S2.pctfilledevals & S1.pctfilledevals)&&&& &&& &&& &&& OR (S2.score = S1.score&&&&&&&& AND S2.pctfilledevals = S1.pctfilledevals&&&&&&&& AND S2.numsessions & S1.numsessions)&&&& &&& &&& &&& OR (S2.score = S1.score&&&&&&&& AND S2.pctfilledevals = S1.pctfilledevals&&&&&&&& AND S2.numsessions = S1.numsessions&&&&&&&& AND S2.speaker & S1.speaker)&&& &&& &&& ) + 1 AS rownum&&& &&& &&& , speaker, track, score, pctfilledevals, numsessions &&& &&& &&& FROM SpeakerStats AS S1 &&& &&& &&& ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
该查询显然比 SQL Server 2005 查询复杂得多。此外，对于 SpeakerStats 表中的每个基础行，SQL Server 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行，平均大约需要扫描该表的一半（最少）行。SQL Server 2005 查询的性能恶化是线性的，而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的。
行号的一个典型应用是通过查询结果分页。给定页大小（以行数为单位）和页号，需要返回属于给定页的行。例如，假设您希望按照“score DESC, speaker”顺序从 SpeakerStats 表中返回第二页的行，并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数，然后只筛选行号为 4 到 6 的行（它们属于第二页）：
SELECT *FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, &&&&&&& speaker, track, score&&&&& FROM SpeakerStats) AS DWHERE rownum BETWEEN 4 AND 6ORDER BY score DESC, speaker以下为结果集：
rownum speaker&&& track&&&&& score------ ---------- ---------- -----------4&&&&& Kathy&&&&& Sys&&&&&&& 85&&&&& Michele&&& Sys&&&&&&& 86&&&&& Mike&&&&&& DB&&&&&&&& 8用更一般的术语表达就是，给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小，以下查询返回属于预期页的行：DECLARE @pagenum AS INT, @pagesize AS INTSET @pagenum = 2SET @pagesize = 3SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum&&& &&& &&& &&& ,speaker&&& &&& &&& &&& , track&&& &&& &&& &&& , score &&& &&& &&& &&& FROM SpeakerStats) &&& &&& &&& &&& AS DWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize &&& &&& &&& &&& ORDER BY score DESC, speaker
上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都 需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时 表，并且对包含这些行号的列进行索引：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *INTO #SpeakerStatsRNFROM SpeakerStatsCREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)然后，对于所请求的每个页，发出以下查询：
SELECT rownum, speaker, track, scoreFROM #SpeakerStatsRNWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesizeORDER BY score DESC, speaker只有属于预期页的行才会被扫描。
分段可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。例如，以下查询按照“score DESC, speaker”顺序单独分配每个 track 内部的行号：
SELECT track, ROW_NUMBER() OVER(&&& PARTITION BY track &&& ORDER BY score DESC, speaker) AS pos, speaker, scoreFROM SpeakerStatsORDER BY track, score DESC, speaker以下为结果集：
track&&&&& pos speaker&&& score---------- --- ---------- -----------DB&&&&&&&& 1&& Suzanne&&& 9DB&&&&&&&& 2&& Mike&&&&&& 8DB&&&&&&&& 3&& Kevin&&&&& 7Dev&&&&&&& 1&& Jessica&&& 9Dev&&&&&&& 2&& Ron&&&&&&& 9Dev&&&&&&& 3&& Joe&&&&&&& 6Dev&&&&&&& 4&& Robert&&&& 6Sys&&&&&&& 1&& Kathy&&&&& 8Sys&&&&&&& 2&& Michele&&& 8Sys&&&&&&& 3&& Brian&&&&& 7Sys&&&&&&& 4&& Dan&&&&&&& 3在 PARTITION BY 子句中指定 track 列会使得为具有相同 track 的每个行组单独计算行号。
RANK, DENSE_RANK
RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：
SELECT speaker, track, score,ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,RANK() OVER(ORDER BY score DESC) AS rnk,DENSE_RANK() OVER(ORDER BY score DESC) AS drnkFROM SpeakerStatsORDER BY score DESC以下为结果集：
speaker&&& track&&&&& score&&&&&& rownum rnk drnk---------- ---------- ----------- ------ --- ----Jessica&&& Dev&&&&&&& 9&&&&&&&&&& 1&&&&& 1&& 1Ron&&&&&&& Dev&&&&&&& 9&&&&&&&&&& 2&&&&& 1&& 1Suzanne&&& DB&&&&&&&& 9&&&&&&&&&& 3&&&&& 1&& 1Kathy&&&&& Sys&&&&&&& 8&&&&&&&&&& 4&&&&& 4&& 2Michele&&& Sys&&&&&&& 8&&&&&&&&&& 5&&&&& 4&& 2Mike&&&&&& DB&&&&&&&& 8&&&&&&&&&& 6&&&&& 4&& 2Kevin&&&&& DB&&&&&&&& 7&&&&&&&&&& 7&&&&& 7&& 3Brian&&&&& Sys&&&&&&& 7&&&&&&&&&& 8&&&&& 7&& 3Joe&&&&&&& Dev&&&&&&& 6&&&&&&&&&& 9&&&&& 9&& 4Robert&&&& Dev&&&&&&& 6&&&&&&&&&& 10&&&& 9&& 4Dan&&&&&&& Sys&&&&&&& 3&&&&&&&&&& 11&&&& 11 5正 如前面讨论的那样，score 列不唯一，因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序，但是具有相同得分的演讲者仍然获得不同的行号。但是请注意，在结果中，所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说，当 ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于，排序代表：具有较高得分的行号加 1，而紧密排序代表：具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中，您可以推导出当 ORDER BY 列表唯一时，ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。
NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码：第一组为 1，第二组为 2，等等。您可以在函数名称后面的括号中指定所请求的组号，在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n，则前面 n 个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照 score 降序将三个组号分配给不同的 speaker 行：
SELECT speaker, track, score,ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,NTILE(3) OVER(ORDER BY score DESC) AS tileFROM SpeakerStatsORDER BY score DESC以下为结果集：
speaker&&& track&&&&& score&&&&&& rownum tile---------- ---------- ----------- ------ ----Jessica&&& Dev&&&&&&& 9&&&&&&&&&& 1&&&&& 1Ron&&&&&&& Dev&&&&&&& 9&&&&&&&&&& 2&&&&& 1Suzanne&&& DB&&&&&&&& 9&&&&&&&&&& 3&&&&& 1Kathy&&&&& Sys&&&&&&& 8&&&&&&&&&& 4&&&&& 1Michele&&& Sys&&&&&&& 8&&&&&&&&&& 5&&&&& 2Mike&&&&&& DB&&&&&&&& 8&&&&&&&&&& 6&&&&& 2Kevin&&&&& DB&&&&&&&& 7&&&&&&&&&& 7&&&&& 2Brian&&&&& Sys&&&&&&& 7&&&&&&&&&& 8&&&&& 2Joe&&&&&&& Dev&&&&&&& 6&&&&&&&&&& 9&&&&& 3Robert&&&& Dev&&&&&&& 6&&&&&&&&&& 10&&&& 3Dan&&&&&&& Sys&&&&&&& 3&&&&&&&&&& 11&&&& 3在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2，这意味着前面 2 个组将获得一个附加行（每个组中有 4 行），而第三个组则不会得到附加行（该组中有 3 行）。组号（tile 号）1 被分配给行 1 到 4，组号 2 被分配给行 5 到 8，组号 3 被分配给行 9 到 11。通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的演讲者，第二个梯级表示具有中等得分的演 讲者，第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义：
SELECT speaker, track, score,CASE NTILE(3) OVER(ORDER BY score DESC)&&& WHEN 1 THEN 'High'&&& WHEN 2 THEN 'Medium'&&& WHEN 3 THEN 'Low'END AS scorecategoryFROM SpeakerStatsORDER BY track, speaker以下为结果集：
speaker&&& track&&&&& score&&&&&& scorecategory---------- ---------- ----------- -------------Kevin&&&&& DB&&&&&&&& 7&&&&&&&&&& MediumMike&&&&&& DB&&&&&&&& 8&&&&&&&&&& MediumSuzanne&&& DB&&&&&&&& 9&&&&&&&&&& HighJessica&&& Dev&&&&&&& 9&&&&&&&&&& HighJoe&&&&&&& Dev&&&&&&& 6&&&&&&&&&& LowRobert&&&& Dev&&&&&&& 6&&&&&&&&&& LowRon&&&&&&& Dev&&&&&&& 9&&&&&&&&&& HighBrian&&&&& Sys&&&&&&& 7&&&&&&&&&& MediumDan&&&&&&& Sys&&&&&&& 3&&&&&&&&&& LowKathy&&&&& Sys&&&&&&& 8&&&&&&&&&& HighMichele&&& Sys&&&&&&& 8&&&&&&&&&& Medium
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具}