OVER Clause是个好东西，常和ROW_NUMBER()、Sum、AVG、Count、Min、Max配合使用
根据SQL官方帮助的实例：
USE AdventureWorks2012;
GO
SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
Row_Number() 以PARTITION BY PostalCode分隔，按照ORDER BY SalesYTD DESC排序
实际效果类似
官方其他例子：
USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
结果：
SalesOrderID ProductID OrderQty Total Avg Count Min Max ------------ ----------- -------- ----------- ----------- ----------- ------ ------ 43659 776 1 26 2 12 1 6 43659 777 3 26 2 12 1 6 43659 778 1 26 2 12 1 6 43659 771 1 26 2 12 1 6 43659 772 1 26 2 12 1 6 43659 773 2 26 2 12 1 6 43659 774 1 26 2 12 1 6 43659 714 3 26 2 12 1 6 43659 716 1 26 2 12 1 6 43659 709 6 26 2 12 1 6 43659 712 2 26 2 12 1 6 43659 711 4 26 2 12 1 6 43664 772 1 14 1 8 1 4 43664 775 4 14 1 8 1 4 43664 714 1 14 1 8 1 4 43664 716 1 14 1 8 1 4 43664 777 2 14 1 8 1 4 43664 771 3 14 1 8 1 4
