我有两个表,一个是产品,一个是价格式类别产品表: ProdudctCode, Descr
体格表: PriceListName, ProductCode, Price现有数据:
产品
AA ProductAA
BB ProductBB价格:FOB ProductAA 100.00
WHO ProductAA 150.00
FOB ProductBB 200.00
WHO ProductBB 250.00 我想要以下查询结婚如何做到?Product Code Product Description FOB WHO ---这是表头AA ProductAA 100.00 150
BB ProductBB 200.00 250.00
体格表: PriceListName, ProductCode, Price现有数据:
产品
AA ProductAA
BB ProductBB价格:FOB ProductAA 100.00
WHO ProductAA 150.00
FOB ProductBB 200.00
WHO ProductBB 250.00 我想要以下查询结婚如何做到?Product Code Product Description FOB WHO ---这是表头AA ProductAA 100.00 150
BB ProductBB 200.00 250.00
FOB=max(case when b.PriceListName='FOB' then Price else 0 end),
WHO=max(case when b.PriceListName='WHO' then Price else 0 end),
from 产品表 a
join 体格表 b on a.ProductCode=b.ProductCode
group by a.ProdudctCode,a.Descr
from
(
select A.ProdudctCode, A.Descr,
case when PriceListName = 'FOB' then Price else 0 end as FOB,
case when PriceListName = 'WHO' then Price else 0 end as WHO
from 产品表 A inner join 体格表 B on B.ProductCode = A.ProductCode
) C
group by C.ProdudctCode, C.Descr
FOB = isnull((select Price from 体格表 n where n.ProductCode = m.ProdudctCode and n.PriceListName = 'FOB'),0),
WHO = isnull((select Price from 体格表 n where n.ProductCode = m.ProdudctCode and n.PriceListName = 'WHO'),0)
from 产品表 m
max(case when b.PriceListName='FOB' then Price else 0 end),
max(case when b.PriceListName='WHO' then Price else 0 end)
from 产品表 a
join 体格表 b on a.Descr=b.ProductCode
group by a.produdctcode, a.Descr