两表查询--高难度请高手进
我有两个表，一个是产品，一个是价格式类别

产品表： 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



[解决办法]

SQL code

select a..ProdudctCode,a.Descr,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 产品表 ajoin 体格表 b on a.ProductCode=b.ProductCodegroup by a.ProdudctCode,a.Descr
[解决办法]
select C.ProductCode, C.Descr as ProductDescription, sum(FOB) as FOB, sum(WHO) as WHO  
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
[解决办法]
SQL code
select m.* ,        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
[解决办法]
select a.ProdudctCode,a.Descr,
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