一道sql面试题目

表A
企业名称 缴费金额 是否缴费
a 10 是
b 10 否
a 20 是
a 30 否
c 10 是
c 20 否

结果：
企业名称 应缴金额 实缴金额 欠费金额 是否欠费
a 60 30 30 是
b 10 10 0 否
c 30 10 20 是


create user test identified by test default tablespace 表空间表;

grant connect,resource to username; --表示把 connect,resource权限授予username用户

grant dba to username; --表示把 dba权限授予给username用户


select c.name,c.total,c.actual,c.remaining,
decode(c.remaining,0,'否', '是') isOwn from
(select a.name name, sum(a.fare) as total,
(select nvl(sum(b.fare),0) from test b where b.name=a.name and b.isfare='Y') actual,
(select nvl(sum(d.fare),0) from test d where d.name=a.name and d.isfare='N') remaining
from test a
group by a.name)c
order by c.name