日期:2014-05-17  浏览次数:20364 次

分组统计更新数据问题
表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 
  a
set
  heji=b.zuoyeliang
from
  a
join
  (select zyhao,sum(zuoyeliang) as zuoyeliang from b group b y zyhao)
on
   a.zyhao=b.zyhao

------解决方案--------------------
SQL code

update 表A set heji=b.zuoyeliang
from 表A a
inner join
(select zyhao,sum(zuoyeliang) as zuoyeliang from 表B group by zyhao) b
on a.zyhao=b.zyhao

------解决方案--------------------
SQL code
--1
SELECT  B.zyhao ,
        SUM(zuoyeliang)
FROM    B
WHERE   EXISTS ( SELECT 1
                 FROM   A
                 WHERE  zyhao = B.zyhao )
GROUP BY B.zyhao

--2  
UPDATE  A
SET     A.heji = T.heji
FROM    ( SELECT    B.zyhao ,
                    SUM(zuoyeliang) AS heji
          FROM      B
          WHERE     EXISTS ( SELECT 1
                             FROM   A
                             WHERE  zyhao = B.zyhao )
          GROUP BY  B.zyhao
        ) T
WHERE   A.zyhao = T.zyhao