SQL SERVER开窗函数

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL SERVER开窗函数，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4307字，纯文字阅读大概需要7分钟。

内容图文

SELECT FName, FCITY, FAGE, FSalary,
(
SELECT COUNT(FName) FROM T_Person
WHERE FSALARY<5000
)
FROM T_Person
WHERE FSALARY<5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的SQL语句展示了如果使用开窗函数来实现同样的效果：

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()
FROM T_Person
WHERE FSALARY<5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。
开窗函数的调用格式为：函数名(列) OVER(选项)

我这里使用的是SQL Server 2008 R2,不知道从什么时候开始，SQL SERVER也支持开窗函数中使用ORDER BY子句（注：书本中说MSSQLServer中是不支持开窗函数中使用ORDER BY子句）。不管怎么样，这里我整合了网上的相关内容。也正因为开窗函数支持了ORDER BY子句之后，开窗函数被分为两大类。

第一大类：聚合开窗函数====》聚合函数(列) OVER (选项)，这里的选项可以是PARTITION BY子句，但不可是ORDER BY子句

第二大类：排序开窗函数====》排序函数(列) OVER(选项)，这里的选项可以是ORDER BY子句，也可以是　OVER（PARTITION BY子句　ORDER BY子句），但不可以是PARTITION BY子句

 

聚合开窗函数

 

OVER 关键字表示把聚合函数当成聚合开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做聚合开窗函数。
在上边的例子中，开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

 

PARTITION BY 子句

 

开窗函数的OVER关键字后括号中的可以使用PARTITION BY 子句来定义行的分区来供进行聚合计算。与GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。下面的SQL语句用于显示每一个人员的信息以及所属城市的人员数：

SELECT FName, FCITY, FAGE, FSalary,
COUNT(FName) OVER(PARTITION BY FCITY)
FROM T_Person

OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

SELECT FName,FCITY, FAGE, FSalary,
COUNT(FName) OVER(PARTITION BY FCITY),
COUNT(FName) OVER(PARTITION BY FAGE)
FROM T_Person

 

排序开窗函数

 

对于排序开窗函数来讲，它支持的开窗函数分别为：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）和NTILE（分组排名）。

先看一段SQL语句：

select  FName, FSalary, FCity, FAge,  
row_number() over(order by FSalary) as rownum,  
rank() over(order by FSalary) as rank,  
dense_rank() over(order by FSalary) as dense_rank,  
ntile(6) over(order by FSalary)as ntile 
from  T_Person 
order by  FName  

 执行的结果如下（对于想自己尝试的朋友，那你得辛苦点，下载电子书或者是购买书来学习吧。因为我可是限于篇幅，省略去大部分内容哦）：

 

看到上面的结果了吧，下面来介绍下相关的内容。我们得到的最终结果是按照FName进行升序显示的。

对于row_number() over(order by FSalary) as rownum来说，这个排序开窗函数是按FSalary升序的方式来排序，并得出排序结果的序号

对于rank() over(order by FSalary) as rank来说，这个排序形容函数是按FSalary升序的方式来排序，并得出排序结果的排名号。这个函数求出来的排名结果可以排列，并列排名之后的排名将是并列的排名加上并列数（简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第三名，也就是没有了第二名，但是有两个第一名）

对于dense_rank() over(order by FSalary) as dense_rank来说，这个排序函数是按FSalary升序的方式来排序，并得出排序结果的排名号。这个函数与rand()函数不同在于，并列排名之后的排名只是并列排名加１（简单说每个人只有一种排名，然后出现两个并列第一名的情况，这时候排在两个第一名后面的人将是第二名，也就是两个第一名，一个第二名）

对于ntile(6) over(order by FSalary)as ntile 来说，这个排序函数是按FSalary升序的方式来排序，并得出排序结果的分组数。

排序函数和聚合开窗函数类似，也支持在OVER子句中使用PARTITION BY语句。例如：

select  FName, FSalary, FCity, FAge,  
row_number() over(partition by FName  order by FSalary) as rownum,  
rank() over(partition by FName order by FSalary) as rank,  
dense_rank() over(partition by FName order by FSalary) as dense_rank,  
ntile(6) over(partition by FName order by FSalary)as ntile 
from  T_Person 
order by  FName

 关于PARTITION BY子句，请看上面的介绍，这里就不再累赘了。但是需要注意一点的是，在排序开窗函数中使用PARTITION BY子句需要放置在ORDER　BY子句之前。

SQL SERVER开窗函数

标签：

本文系统来源：http://www.cnblogs.com/jiang-Xin/p/5955623.html

内容总结

以上是互联网集市为您收集整理的SQL SERVER开窗函数全部内容，希望文章能够帮你解决SQL SERVER开窗函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---