排名函数三兄弟,一看名字就知道,都是为了排名而生!但是各自有各自的特色!以下一个例子说明问题!(以下栗子没有使用Partition By 的关键字,整个结果集进行排序)

RANK 每个值一个排名,同样的值排同样的位置,如第一名有2个,下一个值就要排第三,如此类推,表现如下面的 RandNr 列
DENSE_RANK 每个值一个排名,跟Rank 不一致的地方在于它不跳号,会1,2,3 那样排下来
ROW_NUMBER 每行一个排序值,遇到相同的排序条件的时候,按照顺序给值,对应表现如下面RowNr 列
;WITH CTE1(ID,Col1) AS
(
SELECT 1 ,'AA'
UNION ALL
SELECT 1 ,'AA'
UNION ALL
SELECT 2 ,'BB'
UNION ALL
SELECT 3 ,'CC'
UNION ALL
SELECT 3 ,'CC'
UNION ALL
SELECT 4 ,'DD'
UNION ALL
SELECT 5 ,'EE'
)
SELECT RANK() OVER (ORDER BY ID) AS RankNr,
DENSE_RANK() OVER (ORDER BY ID) AS DenseNr,
ROW_NUMBER() OVER (ORDER BY ID) AS RowNr,
*
FROM CTE1 RankNr DenseNr RowNr ID Col1
-------------------- -------------------- -------------------- ----------- ----
1 1 1 1 AA
1 1 2 1 AA
3 2 3 2 BB
4 3 4 3 CC
4 3 5 3 CC
6 4 6 4 DD
7 5 7 5 EE

然后其实说起排名函数,over 子句的作用也是相当关键的。

Over 子句后面的内容基本如下

Over(

Partition By AAA,BBB --表示按照AAA,BBB进行分组,每个分组从1开始计数,如果忽略 Partition By 关键字,就当整个结果集作为一个分组来排序

Order by CCC asc,DDD desc --表示按照ccc,ddd 的执行排序赋予排序值,如果没有特定的排序顺序怎么办呢? 可以使用  (select 1) 或者用 newid() 这个就用于随机排序用的

)

三兄弟讲完了~扯一下其它方面的

然而Over 子句还有一个更有用的用法,当使用窗口聚合函数(不是排序函数了) 的时候。Over 子句除了可以指定分组之外(这个貌似是2012之后的版本才支持,2012之前的版本只支持结果集的全部聚合),

比方说我还是拿回之前生成了500行数据的测试表(数据没有贴完整了),有时候做对比和统计还是相当有用的哟~~~

SELECT ID,
SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Nr1, --从首行累加到当前行
SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Nr2, --前一行和当前行求和
SUM(ID) OVER (ORDER BY (SELECT 1) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) AS Nr3 --后一行和当前行求和
FROM dbo.Tmp123 ID Nr1 Nr2 Nr3
----------- ----------- ----------- -----------
1 1 1 3
2 3 3 5
3 6 5 7
4 10 7 9
5 15 9 11
6 21 11 13
7 28 13 15
8 36 15 17
9 45 17 19
10 55 19 21
11 66 21 23
12 78 23 25
13 91 25 27
14 105 27 29

最新文章

  1. mysql 分页查询
  2. PDO防sql注入原理分析
  3. (转)完全用GNU/Linux工作 by 王珢
  4. java数据结构_附12_图、顶点和边的定义(双链存储)
  5. attr与prop
  6. [工具]Mac平台开发几个网络抓包工具(sniffer)
  7. SQL 标量函数-----日期函数datediff()、 day() 、month()、year()
  8. log4net使用(winform)
  9. 【待补】java开发Web Service
  10. initial,常用于消除css格式
  11. eclipse中tomcat启动项目 修改java代码不重启服务
  12. B计划
  13. python中的subprocess.Popen()使用
  14. jquery楼层效果
  15. C++智能指针剖析(上)std::auto_ptr与boost::scoped_ptr
  16. sql 表值函数与标量值函数
  17. WTF小程序之animation
  18. kafka监控kafka-eagle 容器化配置
  19. POJ 2449 Remmarguts' Date(第K短路 + A* + 最短路)题解
  20. C#获取电脑硬件信息(CPU ID、主板ID、硬盘ID、BIOS编号)

热门文章

  1. 修改thinkphp路由模式,去掉Home
  2. 高性能javascript学习笔记系列(1) -js的加载和执行
  3. windows 安装 go语言
  4. 轻松掌握:JavaScript享元模式
  5. SQL Server SQL分页查询
  6. 关于mapcontrol和pagelayoutcontrol切换时闪退
  7. 程序新能优化-SQL优化
  8. CRM 2015 快速抓取表单上字段
  9. Atitit.Atiposter 发帖机 信息发布器 v7 q516
  10. 如何更好的通过Inflate layout的方式来实现自定义view