create table acct
(acctid varchar2(10),
bal number,
rate number(4,3),
st date,
et date
)
insert into acct values ('001','500','0.012',to_date('20190101','yyyy-mm-dd'),to_date('20191215','yyyy-mm-dd'));
insert into acct values ('001','1000','0.015',to_date('20191215','yyyy-mm-dd'),to_date('20200116','yyyy-mm-dd'));
insert into acct values ('001','2000','0.015',to_date('20200116','yyyy-mm-dd'),to_date('20201231','yyyy-mm-dd'));
insert into acct values ('002','1500','0.015',to_date('20191231','yyyy-mm-dd'),to_date('20200126','yyyy-mm-dd'));
insert into acct values ('002','10000','0.015',to_date('20200126','yyyy-mm-dd'),to_date('20200306','yyyy-mm-dd'));
insert into acct values ('002','900','0.015',to_date('20200306','yyyy-mm-dd'),to_date('30001231','yyyy-mm-dd'));
select acctid ,sum(
case when st<=to_date('20200101','yyyy-mm-dd') and et>to_date('20200331','yyyy-mm-dd') then round((to_date('20200331','yyyy-mm-dd')-to_date('20200101','yyyy-mm-dd'))*bal*rate/360,2)
when st>to_date('20200101','yyyy-mm-dd') and st<=to_date('20200331','yyyy-mm-dd') and et>to_date('20200331','yyyy-mm-dd') then round((to_date('20200331','yyyy-mm-dd')-st)*bal*rate/360,2)
when st>to_date('20200101','yyyy-mm-dd') and et<=to_date('20200331','yyyy-mm-dd') then round((et-st)*bal*rate/360,2)
when st <=to_date('20200101','yyyy-mm-dd') and et>to_date('20200101','yyyy-mm-dd') and et<=to_date('20200331','yyyy-mm-dd') then round((et-to_date('20200101','yyyy-mm-dd'))*bal*rate/360,2) end )as 利息
from acct group by acctid 查询出的利息和参考答案不同,我的结果是对的吗