oracle的查询语句问题

Question

现在有三张表，分别是用户的点餐表，缴费表，价格表，如何通过这三张表来更新余额表

Answer 1

 create table 表A(
  id int,
  username varchar(20),
  payamount int
);
create table 表B(
  id int,
  username varchar(20),
  dishname varchar(100),
  amount int
);
create table 表C(
  id int,
  dishname varchar(100),
  price int
);
create table 余额表(
  id int,
  username varchar(100),
  left int
);
insert into 表A(id,username,payamount) values(1,'张三',30);
insert into 表A(id,username,payamount) values(2,'李四',33);
insert into 表A(id,username,payamount) values(3,'张三',60);


insert into 表B(id,username,dishname,amount) values(1,'张三','蛋炒饭',2);
insert into 表B(id,username,dishname,amount) values(2,'李四','牛肉面',2);
insert into 表B(id,username,dishname,amount) values(3,'张三','牛肉面',2);


insert into 表C(id,dishname,price) values(1,'蛋炒饭',8);
insert into 表C(id,dishname,price) values(2,'牛肉面',9);


insert into 余额表(id,username) values(1,'张三');

--查询结果
select a.username,a.PAYAMOUNT,b.usemount,a.PAYAMOUNT-b.usemount left from 
(select username,sum(PAYAMOUNT) PAYAMOUNT from 表A a group by username) a,
(
select username,sum((select price from 表c c where c.dishname=b.DISHNAME) * b.Amount) usemount 
from 表b b group by username
) b
where a.username = b.username

--更新数据
update 余额表 set left=(select a.PAYAMOUNT-b.usemount from 
(select username,sum(PAYAMOUNT) PAYAMOUNT from 表A a group by username) a,
(
select username,sum((select price from 表c c where c.dishname=b.DISHNAME) * b.Amount) usemount 
from 表b b group by username
) b
where a.username = b.username and a.username = 余额表.username)

如果没问题，记得采纳一下