復制代碼 代碼如下:

--建立訂單表
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

復制代碼 代碼如下:

--利用窗口函數和聚合開窗函數,可以很快實現上述要求
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

復制代碼 代碼如下:

--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