17,089
社区成员
发帖
与我相关
我的任务
分享
select dt
,new_cust
,new_balance
,sum(new_balance) over(order by dt asc)
from
(
select distinct
dt
,(
select count(1)
from t1 x
where x.dt = t1.dt
and x.custid not in
(
select custid
from t1 y
where y.dt < t1.dt
)
) new_cust
,(
select sum(balance)
from t1 x
where x.dt = t1.dt
and x.custid not in
(
select custid
from t1 y
where y.dt < t1.dt
)
) new_balance
from t1
) ha_ha
create table t1(dt date, custid varchar2(20), balance number);
begin
insert into t1 values(date '2007-01-01', '1001', 100);
insert into t1 values(date '2007-01-01', '1002', 100);
insert into t1 values(date '2007-01-02', '1002', 100);
insert into t1 values(date '2007-01-03', '1003', 100);
insert into t1 values(date '2007-01-04', '1003', 100);
insert into t1 values(date '2007-01-05', '1004', 100);
end;