Sql server 开窗函数over()的语法

用法一：与ROW_NUMBER()函数结合用，给结果进行排序编号，如图：

代码如下：

SELECT ROW_NUMBER() over(order by RequiredDate) num
,* from [Northwind].[dbo].[Orders]

用法二：跟聚合函数一起使用，利用over子句的分组效率比group by子句的效率更高。

在Northwind数据库的订单表Orders中查询”订单id”,”客户id”,”运费”,”所有订单的总数”，“每一个客户的总运费”，“所有客户的总运费”，“每一个客户的平均运费”，“所有客户的平均运费”,”每一个客户所有订单中最大的运费”,”所有客户中最大运费”,”每一个客户所有订单中最小的运费”,”所有客户中最小运费”,如下图:

代码如下：

复制代码
SELECT [OrderID] –订单id
,[CustomerID] –客户id
,[Freight] –运费
,COUNT(OrderID) over() as totalNum –一所有订单的总数
,SUM(Freight) over(partition by customerid) as cusTotalFreight –每一个客户的总运费
,SUM(Freight) over() as totalFreight –所有客户的总运费
,AVG(Freight) over(partition by customerid) as cusAvgFreight –每一个客户的平均运费
,AVG(Freight) over() as avgFreight –所有客户的平均运费
,MAX(Freight) over(partition by customerid) as cusMaxFreight –每一个客户所有订单中最大的运费
,MAX(Freight) over() as maxFreight –所有客户中最大运费
,MIN(Freight) over(partition by customerid) as cusMinFreight –每一个客户所有订单中最小的运费
,MIN(Freight) over() as minFreight –所有客户中最小运费
FROM [Northwind].[dbo].[Orders]
复制代码