学步园

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

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

CREATE TABLE [dbo].[Product](<br /> [ID] [int] NULL, --商品编号<br /> [ProductID] [int] NULL, --商品ID<br /> [BatchNumber] [nchar](50) NULL, --商品批次<br /> [Price] [decimal](18, 2) NULL, --商品价格<br /> [Amount] [int] NULL --商品数量<br /> )<br /> Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (1,1,'000001','5.00','5') --商品1 批次1<br /> Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (2,3,'000004','5.00','2')<br /> Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (3,5,'000001','5.00','2')<br /> Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (4,1,'000003','8.00','7') --商品1 批次2<br /> Insert into Product (ID,ProductID,BatchNumber,Price,Amount)Values (5,7,'000001','5.00','2')<br /> 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