用法一:與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]