请教这个sql怎么写

Question

我的本意是这样 
update  tablea a set a.amount=sum(b.amount_rec) where exists(select sum(b.amount_rec) from table b
where b.aid=a.id group by a.id)
但提示set那不允许使用分组函数
其中
表b外键是a的id，
根据表a的id 对b分组汇总求和，然后更新a的总数字段

Answer 1

--参考：
update tablea a 
set a.amount=(
      select sum(b.amount_rec) from table b
       where b.aid=a.id)
where exists(
      select 1 from table b
      where a.id=b.aid
      )

Answer 2

-- 举个例子给你，如果你还不知道做的话，可以去撞车或是跳楼啦：scott@SZTYORA> desc emp;
 名称                                                                                      是否为空? 类型
 ----------------------------------------------------------------------------------------- -------- ---------------------------------------
 EMPNO                                                                                     NOT NULL NUMBER(4)
 ENAME                                                                                              VARCHAR2(10)
 JOB                                                                                                VARCHAR2(9)
 MGR                                                                                                NUMBER(4)
 HIREDATE                                                                                           DATE
 SAL                                                                                                NUMBER(7,2)
 COMM                                                                                               NUMBER(7,2)
 DEPTNO                                                                                             NUMBER(2)scott@SZTYORA> create table emp_sum(deptno number(2), all_sal number(18,2));
create public synonym EMP_SUM for scott.EMP_SUM表已创建。scott@SZTYORA> l
  1  update emp_sum a
  2* set a.all_sal=(select sum(b.sal) from emp_sum b where b.deptno=a.deptno)
scott@SZTYORA> insert into emp_sum(deptno) select distinct deptno from emp;已创建3行。scott@SZTYORA> select * from emp_sum;    DEPTNO    ALL_SAL
---------- ----------
        30
        20
        10scott@SZTYORA> update emp_sum a
  2  set a.all_sal=(select sum(b.sal) from emp b where b.deptno=a.deptno);已更新3行。scott@SZTYORA> select * from emp_sum;    DEPTNO    ALL_SAL
---------- ----------
        30       9400
        20      15875
        10      12651

Answer 3

-- 就这么简单：
update tablea set a.amount=(select sum(b.amount_rec) from tableb b where b.aid=a.aid);

Answer 4

实际的是这样的，怎么就不对，多个表连接
update  cms_receipt r set r.receipt_amount=(select sum(d.amount_rec) from cms_receipt r,cms_receipt_feecollection c,cms_receipt_flight f,cms_receipt_detail d
where r.receipt_id=c.receipt_id_c and c.feecollection_id=f.feecollection_id and f.flight_id=d.flight_id );

Answer 5

update cms_receipt r 
set r.receipt_amount=(
    select sum(d.amount_rec)
    from cms_receipt_feecollection c,
         cms_receipt_flight f,
         cms_receipt_detail d
    where r.receipt_id=c.receipt_id_c 
      and c.feecollection_id=f.feecollection_id 
      and f.flight_id=d.flight_id );

Answer 6

update cms_receipt r
   set r.receipt_amount = (select sum(d.amount_rec)
                             from cms_receipt_feecollection c,
                                  cms_receipt_flight        f,
                                  cms_receipt_detail        d
                            where r.receipt_id = c.receipt_id_c
                              and c.feecollection_id = f.feecollection_id
                              and f.flight_id = d.flight_id);

Answer 7

无法更新 receipt_amount 为null