select a.*,b.price as price1,c.price as price2,d.price as price3
from item a,itemprice b,itemprice c,itemprice d
where a.itemid=b.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=b.itempriceid)=1
and a.itemid=c.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=c.itempriceid)=2
and a.itemid=d.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=d.itempriceid)=3
from item a,itemprice b,itemprice c,itemprice d
where a.itemid=b.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=b.itempriceid)=1
and a.itemid=c.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=c.itempriceid)=2
and a.itemid=d.itemid and (select count(*) from itemprice
where itemid=a.itemid
and itempriceid<=d.itempriceid)=3
select T1.ItemID
,T1.ItemNo
,T1.ItemName
,sum(case T2.ItemPriceID when 1 then T2.Price else 0 end) as Price1
,sum(case T2.ItemPriceID when 2 then T2.Price else 0 end) as Price2
,sum(case T2.ItemPriceID when 3 then T2.Price else 0 end) as Price3
from Item T1
inner join ItemPrice T2
on T1.ItemID = T2.ItemID
Group By T1.ItemID
,T1.ItemNo
,T1.ItemName
--没测试,出错的话自己改改