1.簡介： SQL Server 2005中的窗口函數幫助你迅速查看不同級別的聚合，通過它可以非常方便地累計總數、移動平均值、以及執行其它計算。
窗口函數功能非常強大，使用起來也十分容易。可以使用這個技巧立即得到大量統計值。
窗口是用戶指定的一組行。 開窗函數計算從窗口派生的結果集中各行的值。
2.適用范圍：
排名開窗函數和聚合開窗函數.
也就是說窗口函數是結合排名開窗函數或者聚合開窗函數一起使用
OVER子句前面必須是排名函數或者是聚合函數
3.例題： 復制代碼 代碼如下:
--建立訂單表
create table SalesOrder(
OrderID int, --訂單id
OrderQty decimal(18,2) --數量
)
go
--插入數據
insert into SalesOrder
select 1,2.0
union all
select 1,1.0
union all
select 1,3.0
union all
select 2,6.0
union all
select 2,1.1
union all
select 3,8.0
union all
select 3,1.1
union all
select 3,7.0
go
--查詢得如下結果
select * from SalesOrder
go
OrderID OrderQty
----------- ------------
1 2.00
1 1.00
1 3.00
2 6.00
2 1.10
3 8.00
3 1.10
3 7.00
現要求顯示匯總總數，每當所占比例，分組匯總數，每單在各組所占比例，要求格式如下：
OrderID OrderQty 匯總 每單比例 分組匯總 每單在各組比例
1 2.00 29.20 0.0685 6.00 0.3333
1 1.00 29.20 0.0342 6.00 0.1667
1 3.00 29.20 0.1027 6.00 0.5000
2 6.00 29.20 0.2055 7.10 0.8451
2 1.10 29.20 0.0377 7.10 0.1549
3 8.00 29.20 0.2740 16.10 0.4969
3 1.10 29.20 0.0377 16.10 0.0683
3 7.00 29.20 0.2397 16.10 0.4348
復制代碼 代碼如下:
--利用窗口函數和聚合開窗函數,可以很快實現上述要求
select OrderID,OrderQty,
sum(OrderQty) over() as [匯總],
convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每單所占比例],
sum(OrderQty) over(PARTITION BY OrderID) as [分組匯總],
convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每單在各組所占比例]
from SalesOrder
order by OrderID
窗口函數是sql2005新增加的,下面我們看看在sql2000裡面怎麼實現上述的結果：
sql2000的實現步驟較麻煩,先計算出總數，再分組計算匯總，最後連接得到結果
復制代碼 代碼如下:
--sql2000
declare @sum decimal(18,2)
select @sum=sum(OrderQty)
from SalesOrder
--按OrderID，計算每組的總計，然後插入臨時表
select OrderID,sum(OrderQty) as su
into #t
from SalesOrder
group by OrderID
--連接臨時表，得到結果
select s.OrderID,s.OrderQty,
@sum as [匯總],
convert(decimal(18,4),s.OrderQty/@sum) as [每單所占比例],
t.su as [分組匯總],
convert(decimal(18,4),s.OrderQty/t.su) as [每單在各組所占比例]
from SalesOrder s join #t t
on t.OrderID=s.OrderID
order by s.OrderID
drop table #t
go
上面演示的都是窗口函數與聚合開窗函數的使用，它與排名開窗函數請看下面例題:
復制代碼 代碼如下:
--與排名開窗函數使用
select OrderID,OrderQty,
rank() over(PARTITION BY orderid order by OrderQty ) as [分組排名],
rank() over(order by OrderQty ) as [排名]
from SalesOrder
order by orderid asc
--查詢得如下結果
OrderID OrderQty 分組排名 排名
1 2.00 2 4
1 3.00 3 5
1 1.00 1 1
2 1.10 1 2
2 6.00 2 6
3 7.00 2 7
3 8.00 3 8
3 1.10 1 2
