declare @str varchar(8000) set @str='select productid,[avgprice]=avg(price)' select @str=@str+',[avgprice'+cast(vendorid as varchar)+']=sum(case vendorid when '+ cast(vendorid as varchar)+' then price else 0 end)/sum(case vendorid when '+ cast(vendorid as varchar)+' then 1 else 0 end)' from stockdetail group by vendorid set @str=@str+' from StockDetail group by productid' exec(@str)
假设你的Vendor表里面的数据为 VendorId 1 2 3 。则可以这么写: select ProductId , AvgPrice=avg(Price), AvgPriceOfVendor1=sum(case VendorId when 1 then Price end) --里面的1表示Vendor表里面VendorId的值 AvgPriceOfVendor2=sum(case VendorId when 2 then Price end) from StockDetail group by ProductId
set @str='select productid,[avgprice]=avg(price)'
select @str=@str+',[avgprice'+cast(vendorid as varchar)+']=sum(case vendorid when '+
cast(vendorid as varchar)+' then price else 0 end)/sum(case vendorid when '+
cast(vendorid as varchar)+' then 1 else 0 end)' from stockdetail group by vendorid
set @str=@str+' from StockDetail group by productid'
exec(@str)
VendorId
1
2
3
。则可以这么写:
select ProductId ,
AvgPrice=avg(Price),
AvgPriceOfVendor1=sum(case VendorId when 1 then Price end)
--里面的1表示Vendor表里面VendorId的值
AvgPriceOfVendor2=sum(case VendorId when 2 then Price end)
from StockDetail group by ProductId