黑马程序员技术交流社区

标题: OVER子句中排序函数的用法 [打印本页]

---

作者: 叶彦召    时间: 2012-2-29 00:39
标题: OVER子句中排序函数的用法
        OVER子句支持四种排名函数：ROW_NUMBER()、RANK()、DENSE_RANK()、NTIL()。
先直接给一个例子吧：
select orderid,custid,val,
        ROW_NUMBER() OVER(ORDER BY val) as rownum,
        RANK() OVER(ORDER BY val) as rank,
        DENSE_RANK() OVER(ORDER BY val) as dense_rank,
        NTILE(10) OVER(ORDER BY val) as ntile
from OrderValue;
查询结果如下图所示：



ROW_NUMBER函数用于为查询的结果集中的各行分配递增的序列号，其逻辑顺序通过OVER子句中的ORDER BY语句进行指定。
RANK 表示之前有多少行具有更低的排序值
DENSE_RANK表示之前有多少个具有更低的排序值
NTILE函数把结果中的行关联到组，并为每一行分配一个所属组的编号。NTILE（）函数的参数就是要设置分成几组，本例子中设置分配为10个组，查询总共有40行，所以每个组有4行数据。

和聚合函数一样，排名函数也支持在OVER子句中使用PARTITION BY语句。例如，与在整个集合中分配行号不同，表达式ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val)为各行中具有相同custid的子集独立的分配行号。在一下查询中使用了这个表达式：
select orderid,custid,val,
        ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) as rownum
from OrderValue;
查询结果如下图：



从这些输出中可以看到，行号是为每个客户独立计算的。

注意：以上查询结果并不是唯一的，因为在查询中over子句中的order by语句指定的排序字段是val，而从以上结果中可以看出val的值有一些行是相同的。 例如，价格为35.36的行，第三行和第四行的顺序发生以下变化也是正确的。所以，如果想让结果唯一，则须在ORDER BY列表中添加元素，让它具有唯一性。


该贴已经同步到 叶彦召的微博

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2