这条SQL如何改？

[chrischen79]

select OPOR.DocEntry, DocDueDate, ItemCode, Dscription, Quantity,
(POR1.Quantity - ISNULL((select SUM(U_Cnt) from [@ENT_QMMR] where U_CsCode = OPOR.CardCode and U_RecDt = OPOR.DocDueDate and U_ItemCode = POR1.ItemCode) , 0))
as Cnt1 from OPOR
join POR1 on OPOR.DocEntry = POR1.DocEntry
where OPOR.DocEntry = 1
order by LineNum

运行的时候报错：
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

[guantao1983]

新手 帮你顶一下

[yelook]

select OPOR.DocEntry, DocDueDate, ItemCode, Dscription, Quantity,
(POR1.Quantity - ISNULL(c.num,0)) as Cnt1 from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry left join
(select isnull(SUM(U_Cnt),0) as num,U_CsCode,U_RecDt,U_ItemCode from [@ENT_QMMR] group by U_CsCode,U_RecDt,U_ItemCode) c on
OPOR.CardCode=c.U_CsCode and OPOR.DocDueDate = c.U_RecDt and POR1.ItemCode=c.U_ItemCode
where OPOR.DocEntry = 1
order by LineNum

[Andy__Huang]

你的子查詢那樣寫有問題﹐應該把OPOR﹑POR1的查詢結果做為一個子表﹐然后再與表[@ENT_QMMR]做關聯查詢

你那樣寫子查詢是錯誤的。
如果你能夠把你的表結構帖出來﹐那更好說明問題

[pjy]

(select SUM(U_Cnt) from [@ENT_QMMR] where U_CsCode = OPOR.CardCode and U_RecDt = OPOR.DocDueDate and U_ItemCode = POR1.ItemCode)
中怎么可以用POR1表了，你写这个是 from OPOR呀!

[逍遥的心]

select OPOR.DocEntry, DocDueDate, ItemCode, Dscription, Quantity,
(POR1.Quantity - ISNULL(c.num,0)) as Cnt1 from OPOR inner join POR1 on OPOR.DocEntry = POR1.DocEntry left join
(select isnull(SUM(U_Cnt),0) as num,U_CsCode,U_RecDt,U_ItemCode from [@ENT_QMMR] group by U_CsCode,U_RecDt,U_ItemCode) c on
OPOR.CardCode=c.U_CsCode and OPOR.DocDueDate = c.U_RecDt and POR1.ItemCode=c.U_ItemCode
where OPOR.DocEntry = 1
order by LineNum

[chrischen79]

谢谢各位，结帐！