1.(select a.cBusType as 类别,a.cBusCode as 业务号,b.cInvCode as 产品编码,b.iUnitCost as 单价 from RdRecord a left join RdRecords b on a.id=b.id where a.dDate>='2011-10-01' and a.cBusType ='调拨出库')ck2.(select a.cBusType as 类别,a.cBusCode as 业务号,b.cInvCode as 产品编码,b.iUnitCost as 单价 from RdRecord a left join RdRecords b on a.id=b.id where a.dDate>='2011-10-01' and a.cBusType ='调拨入库')rk3.update RdRecords set rk.单价=ck.单价 where rk.业务号=ck.业务号 and rk.产品编码=ck.产品编码
UPDATE的和SELECT怎么合并哦?
SET iUnitCost=c.单价
from RdRecord a
inner join RdRecords b on a.id=b.id
INNER JOIN
(select a.cBusCode as 业务号,b.cInvCode as 产品编码,b.iUnitCost as 单价
from RdRecord a left join RdRecords b on a.id=b.id
where a.dDate>='2011-10-01' and a.cBusType ='调拨出库') AS c
ON c.业务号=a.cBusCode AND c.产品编码=b.cInvCode
where a.dDate>='2011-10-01' and a.cBusType ='调拨入库'
from (select a.cBusType as 类别,a.cBusCode as 业务号,b.cInvCode as 产品编码,b.iUnitCost as 单价 from RdRecord a left join RdRecords b on a.id=b.id where a.dDate>='2011-10-01' and a.cBusType ='调拨出库')ck
join (select a.cBusType as 类别,a.cBusCode as 业务号,b.cInvCode as 产品编码,b.iUnitCost as 单价 from RdRecord a left join RdRecords b on a.id=b.id where a.dDate>='2011-10-01' and a.cBusType ='调拨入库')rk
on (rk.业务号=ck.业务号 and rk.产品编码=ck.产品编码)
看大版的吧
(select iUniCost fromRdRecords a from RdRecord a left join RdRecords b on a.id=b.id where a.dDate>='2011-10-01' and a.cBusType ='调拨出库' and a.cBusCode =c.cBusCode and b.cInvCode=c.cInvCode)
where c.id in (select d.id from RdRecord d where d.id=c.id and d.dDate>='2011-10-01' and d.cBusType ='调拨入库')这个应该能实现,不过效率很低