SELECT 查询如下,是没有什么问题的.
SELECT Mbae.ae07,Mbae.ID,SUM(Qcount.ga91) SUMga91
FROM Mbae INNER JOIN Qcount ON Mbae.ae01=Qcount.ga51 AND Mbae.ae09=Qcount.ga06
AND Mbae.ae02=Qcount.ga07 AND Mbae.ae05=Qcount.ga08
GROUP BY Mbae.ae07,Mbae.ID
现在我要写成UPDATE 如下
UPDATE Mbae SET Mbae.ae07=SUM(Qcount.ga91)
FROM Mbae INNER JOIN Qcount ON Mbae.ae01=Qcount.ga51 AND Mbae.ae09=Qcount.ga06
AND Mbae.ae02=Qcount.ga07 AND Mbae.ae05=Qcount.ga08
GROUP BY Mbae.ID
老是显示 关键字 'GROUP' 附近有语法错误。 大家帮忙看一下这个UPDATE SQL 如何写, 谢谢.
------解决思路----------------------
你把你上面的汇总语句当做一个临时表 ，更新的时候利用这个临时表更新即可，update 语句是不可以直接group by 的。
因为没你具体表 随便写了个格式供参考：
UPDATE Mbae SET Mbae.ae07=TAB.SUMga91 FROM (SELECT Mbae.ae07,Mbae.ID,SUM(Qcount.ga91) SUMga91
FROM Mbae INNER JOIN Qcount ON Mbae.ae01=Qcount.ga51 AND Mbae.ae09=Qcount.ga06
AND Mbae.ae02=Qcount.ga07 AND Mbae.ae05=Qcount.ga08
GROUP BY Mbae.ae07,Mbae.ID) AS TAB
WHERE Mbae.ae01=TAB.ga51 AND Mbae.ae09=TAB.ga06
AND Mbae.ae02=TAB.ga07 AND Mbae.ae05=TAB.ga08
------解决思路----------------------
with cte as (
SELECT Mbae.ae07,Mbae.ID,SUM(Qcount.ga91) SUMga91
FROM Mbae INNER JOIN Qcount ON Mbae.ae01=Qcount.ga51 AND Mbae.ae09=Qcount.ga06
AND Mbae.ae02=Qcount.ga07 AND Mbae.ae05=Qcount.ga08
GROUP BY Mbae.ae07,Mbae.ID
)
UPDATE cte SET ae07=SUMga91