1013
查詢表某列的加權平均值
以一個簡單商品表為例，商品表包含商品編號，批次，數量，價格等字段，現在想要查詢不同批次商品的加權平均價，具體問題描述如下：
建表語句(展開-複製-運行即可初始化數據)：
CREATE TABLE [dbo].[Product](
[ID] [int] NULL, --商品編號
[ProductID] [int] NULL, --商品ID
[BatchNumber] [nchar](50) NULL, --商品批次
[Price] [decimal](18, 2) NULL, --商品價格
[Amount] [int] NULL --商品數量
)
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (1,1,'000001','5.00','5') --商品1 批次1
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (2,3,'000004','5.00','2')
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (3,5,'000001','5.00','2')
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (4,1,'000003','8.00','7') --商品1 批次2
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (5,7,'000001','5.00','2')
Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (6,1,'000005','7.00','3') --商品1 批次3
表數據如下：
每個商品有不同的批次，每個批次又有不同的價格
，
如下圖所示：
現在要實現的查詢是： 根據商品ID，查詢出該商品的所有批次及數量，以及加權平均價格；
查詢顯示結果如下：
分析過程：
1.銷售總量字段容易被查出了，一個Sum語句就夠了，難點在於將批次(字符串)求和。
解決思路 ：專門寫一個自定義函數來獲取批次及數量的疊加內容：
CREATE FUNCTION GetString
(@id int)
RETURNS nvarchar(500) AS
BEGIN
declare @all nvarchar(500)
set @all=''
select @all=@all+ p.BatchNumber+'('+cast(sum(p.Amount)as char(100))+')'+','
from Product as p where ProductID=@id
group by p.BatchNumber
return @all
END
2.考慮加權平均價的計算，這裏可以用臨時表的方法實現。
第一步 ：增加一個計算列，總價-total
select *,Price*Amount as total from product where ProductID=1
第二步： 查詢出加權平均價
select sum(total)/sum(Amount) from(select *,Price*Amount as total from product where ProductID=1)temp
第三步 ：將查詢語句集中起來得到查詢結果
select replace(dbo.GetString
(P.ProductID),' ','')as '批次及數量',
sum(P.Amount)as '總銷售量',
(select sum(total)/sum(Amount)from(select *,Price*Amount as total from product where ProductID=1)temp)as '加權平均價'
from product as P
where ProductID=1
group by ProductID
注： replace函數用來除去查詢結果中的空字符。
小結
：運行下麵代碼，即可查詢結果。
select replace(dbo.GetString
(P.ProductID),' ','')as '批次及數量',
sum(P.Amount)as '總銷售量',
(select sum(total)/sum(Amount)from(select *,Price*Amount as total from product where ProductID=1)temp)as '加權平均價'
from product as P
where ProductID=1
group by ProductID
最後更新：2017-04-02 06:51:30