利用over(),將統(tǒng)計(jì)信息計(jì)算出來,然后直接篩選結(jié)果集
declare @t table(
ProductID int,
ProductName varchar(20),
ProductType varchar(20),
Price int)
insert @t
select 1,'name1','P1',3 union all
select 2,'name2','P1',5 union all
select 3,'name3','P2',4 union all
select 4,'name4','P2',4
查詢要求:查出每類產(chǎn)品中價(jià)格最高的信息
--做法一:找到每個(gè)組里,價(jià)格最大的值;然后再找出每個(gè)組里價(jià)格等于這個(gè)值的
--缺點(diǎn):要進(jìn)行一次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(),將統(tǒng)計(jì)信息計(jì)算出來,然后直接篩選結(jié)果集。
--over() 可以讓函數(shù)(包括聚合函數(shù))與行一起輸出。
;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() 前面是一個(gè)函數(shù),如果是聚合函數(shù),那么order by 不能一起使用。
--over() 的另一常用情景是與 row_number() 一起用于分頁。
現(xiàn)在來介紹一下開窗函數(shù)。
窗口函數(shù)OVER()指定一組行,開窗函數(shù)計(jì)算從窗口函數(shù)輸出的結(jié)果集中各行的值。
開窗函數(shù)不需要使用GROUP BY就可以對(duì)數(shù)據(jù)進(jìn)行分組,還可以同時(shí)返回基礎(chǔ)行的列和聚合列。
1.排名開窗函數(shù)
ROW_NUMBER、DENSE_RANK、RANK、NTILE屬于排名函數(shù)。
排名開窗函數(shù)可以單獨(dú)使用ORDER BY 語句,也可以和PARTITION BY同時(shí)使用。
PARTITION BY用于將結(jié)果集進(jìn)行分組,開窗函數(shù)應(yīng)用于每一組。
ODER BY 指定排名開窗函數(shù)的順序。在排名開窗函數(shù)中必須使用ORDER BY語句。
例如查詢每個(gè)雇員的定單,并按時(shí)間排序
;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
窗口函數(shù)根據(jù)PARTITION BY語句按雇員ID對(duì)數(shù)據(jù)行分組,然后按照ORDER BY 語句排序,排名函數(shù)ROW_NUMBER()為每一組的數(shù)據(jù)分從1開始生成一個(gè)序號(hào)。
ROW_NUMBER()為每一組的行按順序生成一個(gè)唯一的序號(hào)
RANK()也為每一組的行生成一個(gè)序號(hào),與ROW_NUMBER()不同的是如果按照ORDER BY的排序,如果有相同的值會(huì)生成相同的序號(hào),并且接下來的序號(hào)是不連序的。例如兩個(gè)相同的行生成序號(hào)3,那么接下來會(huì)生成序號(hào)5。
DENSE_RANK()和RANK()類似,不同的是如果有相同的序號(hào),那么接下來的序號(hào)不會(huì)間斷。也就是說如果兩個(gè)相同的行生成序號(hào)3,那么接下來生成的序號(hào)還是4。
NTILE (integer_expression) 按照指定的數(shù)目將數(shù)據(jù)進(jìn)行分組,并為每一組生成一個(gè)序號(hào)。
2.聚合開窗函數(shù)
很多聚合函數(shù)都可以用作窗口函數(shù)的運(yùn)算,如SUM,AVG,MAX,MIN。
聚合開窗函數(shù)只能使用PARTITION BY子句或都不帶任何語句,ORDER BY不能與聚合開窗函數(shù)一同使用。
例如,查詢雇員的定單總數(shù)及定單信息
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
如果窗口函數(shù)不使用PARTITION BY 語句的話,那么就是不對(duì)數(shù)據(jù)進(jìn)行分組,聚合函數(shù)計(jì)算所有的行的值
WITH OrderInfo AS
(
SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)
)
總結(jié)
以上所述是小編給大家介紹的Sql Server 開窗函數(shù)Over()的使用實(shí)例詳解,希望對(duì)大家有所幫助,如果大家有任何疑問請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持!
您可能感興趣的文章:- SQL中的開窗函數(shù)詳解可代替聚合函數(shù)使用
- SQL Server 2012 開窗函數(shù)
- sql server如何利用開窗函數(shù)over()進(jìn)行分組統(tǒng)計(jì)
- Oracle數(shù)據(jù)庫中SQL開窗函數(shù)的使用