聚合不应出现在 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