利用over()，將統計信息計算出來，然後直接篩選結果集 1 declare @t table( 2 ProductID int, 3 ProductName varchar(20), 4 ProductType varchar(20), 5 Price int) 6 7 insert @t 8 s ...
利用over()，將統計信息計算出來，然後直接篩選結果集
1 declare @t table( 2 ProductID int, 3 ProductName varchar(20), 4 ProductType varchar(20), 5 Price int) 6 7 insert @t 8 select 1,'name1','P1',3 union all 9 select 2,'name2','P1',5 union all 10 select 3,'name3','P2',4 union all 11 select 4,'name4','P2',4
查詢要求：查出每類產品中價格最高的信息
--做法一：找到每個組裡,價格最大的值；然後再找出每個組裡價格等於這個值的
--缺點:要進行一次join
select t1.* from @t t1 join (select ProductType, max(Price) Price from @t group by ProductType) t2 on t1.ProductType = t2.ProductType where t1.Price = t2.Price order by ProductType
--做法二：利用over()，將統計信息計算出來，然後直接篩選結果集。
--over() 可以讓函數(包括聚合函數)與行一起輸出。
;with cte as(select *, max(Price) over(partition by (ProductType)) MaxPrice from @t) select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice order by ProductType
-over() 的語法為：over([patition by ] <order by >)。需要註意的是，over() 前面是一個函數，如果是聚合函數，那麼order by 不能一起使用。
--over() 的另一常用情景是與 row_number() 一起用於分頁。
現在來介紹一下開窗函數。
視窗函數OVER（）指定一組行，開窗函數計算從視窗函數輸出的結果集中各行的值。
開窗函數不需要使用GROUP BY就可以對數據進行分組，還可以同時返回基礎行的列和聚合列。
1．排名開窗函數
ROW_NUMBER、DENSE_RANK、RANK、NTILE屬於排名函數。排名開窗函數可以單獨使用ORDER BY 語句，也可以和PARTITION BY同時使用。
PARTITION BY用於將結果集進行分組，開窗函數應用於每一組。
ODER BY 指定排名開窗函數的順序。在排名開窗函數中必須使用ORDER BY語句。
例如查詢每個雇員的定單，並按時間排序
;WITH OrderInfo AS ( SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number, OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) ) SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDate From OrderInfo WHERE Number BETWEEN 0 AND 10
視窗函數根據PARTITION BY語句按雇員ID對數據行分組，然後按照ORDER BY 語句排序，排名函數ROW_NUMBER()為每一組的數據分從1開始生成一個序號。
ROW_NUMBER（）為每一組的行按順序生成一個唯一的序號
RANK（）也為每一組的行生成一個序號，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會生成相同的序號，並且接下來的序號是不連序的。例如兩個相同的行生成序號3，那麼接下來會生成序號5。
DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號，那麼接下來的序號不會間斷。也就是說如果兩個相同的行生成序號3，那麼接下來生成的序號還是4。
NTILE (integer_expression) 按照指定的數目將數據進行分組，併為每一組生成一個序號。
2．聚合開窗函數
很多聚合函數都可以用作視窗函數的運算，如SUM,AVG,MAX,MIN。聚合開窗函數只能使用PARTITION BY子句或都不帶任何語句，ORDER BY不能與聚合開窗函數一同使用。
例如，查詢雇員的定單總數及定單信息
WITH OrderInfo AS ( SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) ) SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCount From OrderInfo ORDER BY EmployeeID
如果視窗函數不使用PARTITION BY 語句的話，那麼就是不對數據進行分組，聚合函數計算所有的行的值
WITH OrderInfo AS ( SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK) )
