当前位置: 代码迷 >> Sql Server >> 带 GROUP BY 无法用于update SQL语句吗,该如何处理
  详细解决方案

带 GROUP BY 无法用于update SQL语句吗,该如何处理

热度:424   发布时间:2016-04-27 12:20:34.0
带 GROUP BY 无法用于update SQL语句吗
以下SQL 没问题,正确,但是我的目的是更新
SELECT A.Epuid,MAX(B.intb),SUM(C.intb)
FROM Frdrecordson A LEFT JOIN Epurchaseson B on A.Epuid=B.ID 
LEFT JOIN Frdrecordson C on A.Epuid=C.Epuid
WHERE A.stcode='A1205-0013' AND A.Epuid>0
GROUP BY A.Epuid HAVING SUM(C.intb)>=MAX(B.intb)

所以我演变如下SQL语句
UPDATE B SET B.stsb = '结束'
FROM Frdrecordson A LEFT JOIN Epurchaseson B on A.Epuid=B.ID 
LEFT JOIN Frdrecordson C on A.Epuid=C.Epuid
WHERE A.stcode='A1205-0013' AND A.Epuid>0
GROUP BY A.Epuid HAVING SUM(C.intb)>=MAX(B.intb)

当然报错了,语法错误,请高人帮我修改,总之要满足SUM(C.intb)>=MAX(B.intb) 才更新

------解决方案--------------------
SQL code
UPDATE B SET B.stsb = '结束'FROM binner join (SELECT A.Epuid,MAX(B.intb),SUM(C.intb)        FROM Frdrecordson A         LEFT JOIN Epurchaseson B on A.Epuid=B.ID          LEFT JOIN Frdrecordson C on A.Epuid=C.Epuid        WHERE A.stcode='A1205-0013' AND A.Epuid>0        GROUP BY A.Epuid         HAVING SUM(C.intb)>=MAX(B.intb)) A on A.epuid=b.id
------解决方案--------------------
探讨

我明白一楼的意思,我改成
SELECT E.* FROM Epurchaseson E INNER JOIN
(SELECT A.Epuid,MAX(B.intb),SUM(C.intb) FROM Frdrecordson A LEFT JOIN Epurchaseson B on A.Epuid=B.ID
LEFT JOIN Frdrecordson C on A.Epuid=……

------解决方案--------------------
探讨

我知道原因了,改成如下
SELECT E.* FROM Epurchaseson E INNER JOIN
(SELECT A.Epuid,MAX(B.intb) AS SUMbintb,SUM(C.intb) AS SUMcintb FROM Frdrecordson A LEFT JOIN Epurchaseson B on A.Epuid=B.ID
LEFT JOIN Frdr……

------解决方案--------------------
楼上的解决方案很好
  相关解决方案