Oracle 两表查询统计
发布网友
发布时间:2022-05-18 22:05
我来回答
共1个回答
热心网友
时间:2023-11-09 23:37
建表及记录
create table a (id int,总金额 int);
insert into a values (1,10000);
insert into a values (2,20000);
insert into a values (3,30000);
create table b(aid int,消费金额 int);
insert into b values (1,300);
insert into b values (1,500);
insert into b values (2,100);
insert into b values (1,2000);
commit;
执行
select a.id,a.总金额,sum(nvl(a.总金额,0)-nvl(c.消费金额,0)) 剩余金额 from a
left join (select aid,sum(b.消费金额) 消费金额 from b group by aid) c on a.id=c.aid group by a.id,a.总金额
结果