查询表某列的加权平均值

以一个简单商品表为例，商品表包含商品编号，批次，数量，价格等字段，现在想要查询不同批次商品的加权平均价，具体问题描述如下：

建表语句(展开-复制-运行即可初始化数据)：

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