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

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

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