聚合不应出现在 UPDATE 语句的集合列表中 的问题求解
聚合不应出现在 UPDATE 语句的集合列表中。
update IA set ageStage2=max(case when MA.ageStage=2 then MA.quantity else 0 end), ageStage4=max(case when MA.ageStage=4 then MA.quantity else 0 end) from #materialAge as MA,#inventoryAge as IA
where MA.resourceID=IA.resourceID

坐等大神帮忙解答

[解决办法]

SQL code

update IA set ageStage2=MA.ageStage2, ageStage4=MA.ageStage4from (SELECT resourceID, ageStage2=max(case when ageStage=2 then quantity else 0 end), ageStage4=max(case when ageStage=4 then quantity else 0 end) FROM #materialAgeGROUP BY resourceID) as MA,#inventoryAge as IAwhere MA.resourceID=IA.resourceID
[解决办法]
SQL code
update IA set ageStage2=(select max(case when MA.ageStage=2 then MA.quantity else 0 end) from #materialAge ), ageStage4=(select max(case when MA.ageStage=4 then MA.quantity else 0 end) from #materialAge )from #materialAge as MA,#inventoryAge as IAwhere MA.resourceID=IA.resourceID