表A: 三个字段 BHID(int),自增型 ,ZYHao(string)关键字, Heji (decimal);
表B: 三个字段 Id(int),自增型,关键字, ZYHao(string), ZuoYeLiang (decimal);
如何根据表A的ZYhao,来汇总表B中的zuoyeliang,并根据表A的zyhao,来更新表A的heji值?
------解决方案--------------------
- SQL code
update aset heji=b.zuoyeliangfrom ajoin (select zyhao,sum(zuoyeliang) as zuoyeliang from b group b y zyhao)on a.zyhao=b.zyhao
------解决方案--------------------
- SQL code
update 表A set heji=b.zuoyeliangfrom 表A ainner join(select zyhao,sum(zuoyeliang) as zuoyeliang from 表B group by zyhao) bon a.zyhao=b.zyhao
------解决方案--------------------
- SQL code
--1SELECT B.zyhao , SUM(zuoyeliang)FROM BWHERE EXISTS ( SELECT 1 FROM A WHERE zyhao = B.zyhao )GROUP BY B.zyhao--2 UPDATE ASET A.heji = T.hejiFROM ( SELECT B.zyhao , SUM(zuoyeliang) AS heji FROM B WHERE EXISTS ( SELECT 1 FROM A WHERE zyhao = B.zyhao ) GROUP BY B.zyhao ) TWHERE A.zyhao = T.zyhao