问题描述

oracle的查询语句问题

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

解决方案

 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)

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

解决方案二：


如图

解决方案三：

Oracle 查询语句
Oracle语句及查询结果
oracle 更新，查询语句 与约束问题