记得提供建表脚本和测试数据：
/thread-1348543-1-1.html
create table t_deposit
(
name varchar2(10),
amount number,
d_date date
);
with t as (select name, amount, trunc(sysdate-d_date) days from t_deposit)
select name,
sum(d0to30) as d0to30,
sum(d30to90) as d30to90,
sum(d90to120) as d90to120,
sum(d120to0) as d120to0
from
(
select name,
case when days<=30 then amount else 0 end as d0to30,
case when days>30 and days <=90 then amount else 0 end as d30to90,
case when days>90 and days <=120 then amount else 0 end as d90to120,
case when days>120 then amount else 0 end as d120to0
from t
)group by name;
insert into t_deposit (d_date, name, amount)
values (to_date('-01-10','YYYY-MM-DD'), 'A', 100);
insert into t_deposit(d_date, name, amount)
values (to_date('-02-18','YYYY-MM-DD'), 'A', 200);
insert into t_deposit(d_date, name, amount)
values (to_date('-03-18','YYYY-MM-DD'), 'A', 200);
insert into t_deposit(d_date, name, amount)
values (to_date('-04-18','YYYY-MM-DD'), 'A', 200);
insert into t_deposit(d_date, name, amount)
values (to_date('-04-30','YYYY-MM-DD'), 'A', 280);
with t as (select name, amount, trunc(sysdate-d_date) days from t_deposit)
select name,
sum(d0to30) as d0to30,
sum(d30to90) as d30to90,
sum(d90to120) as d90to120,
sum(d120to0) as d120to0
from
(
select name,
case when days<=30 then amount else 0 end as d0to30,
case when days>30 and days <=90 then amount else 0 end as d30to90,
case when days>90 and days <=120 then amount else 0 end as d90to120,
case when days>120 then amount else 0 end as d120to0
from t
)group by name;
[本帖最后由 guostong 于 -7-15 05:25 编辑]
