select fproID,fProN,fLastMonthSurplusQuantity,(select sum(fQuantity) from Ordertable group by fProID having fproID=a.fproID) fInQuantity,(select sum(fQuantity) from PickUPWHtable group by fProID having fproID=a.fproID ) fOutQuantity, (select sum(fQuantity) from ReturnWHtableForPick group by fProID having fproID=a.fproID ) fReturnQuantity,/*下面这句怎么写?上面的两种方法都有错误提示:列名 'fInQuantity' 无效。 服务器: 消息 207,级别 16,状态 1,行 1 列名 'fOutQuantity' 无效。 服务器: 消息 207,级别 16,状态 1,行 1 列名 'fReturnQuantity' 无效。*/ [fThisMonthSurplusQuantity]=fLastMonthSurplusQuantity+fInQuantity+fOutQuantity+fReturnQuantity , flendQuantity from stockTable a where fsuppiler='1'谢谢
select fproID,fProN,fLastMonthSurplusQuantity,fInQuantity,fOutQuantity,fReturnQuantity, [fThisMonthSurplusQuantity]=fLastMonthSurplusQuantity+fInQuantity+fOutQuantity+fReturnQuantity , flendQuantity from stockTable a left join (select sum(fQuantity) finquantity,fproid from Ordertable group by fProID ) as aa on aa.fproID=a.fproID left join (select sum(fQuantity) foutquantity,fproid from PickUPWHtable group by fProID) as bbb on bb.fproID=a.fproID left join (select sum(fQuantity) fReturnQuantity,fproid from ReturnWHtableForPick group by fProID ) as ccc on ccc.fproID=a.fproID where fsuppiler='1'
(select sum(fQuantity) from ReturnWHtableForPick group by fProID having fproID=a.fproID ) fReturnQuantity,/*下面这句怎么写?上面的两种方法都有错误提示:列名 'fInQuantity' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'fOutQuantity' 无效。
服务器: 消息 207,级别 16,状态 1,行 1
列名 'fReturnQuantity' 无效。*/
[fThisMonthSurplusQuantity]=fLastMonthSurplusQuantity+fInQuantity+fOutQuantity+fReturnQuantity
, flendQuantity
from stockTable a where fsuppiler='1'谢谢
, flendQuantity
from stockTable a
left join (select sum(fQuantity) finquantity,fproid from Ordertable group by fProID ) as aa on aa.fproID=a.fproID
left join (select sum(fQuantity) foutquantity,fproid from PickUPWHtable group by fProID) as bbb on bb.fproID=a.fproID
left join (select sum(fQuantity) fReturnQuantity,fproid from ReturnWHtableForPick group by fProID ) as ccc on ccc.fproID=a.fproID
where fsuppiler='1'