当前位置: 代码迷 >> Sql Server >> 分组统计更新数据有关问题
  详细解决方案

分组统计更新数据有关问题

热度:21   发布时间:2016-04-27 11:17:56.0
分组统计更新数据问题
表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
  相关解决方案