17,086
社区成员
发帖
与我相关
我的任务
分享
SELECT A.ID,A.FREEDATE,NVL(T.C_SUM,0)
FROM A LEFT JOIN(
SELECT A.ID ID,MAX(B.SUM) C_SUM
FROM B,A
WHERE (B.OPTDATE < A.FREEDATE) AND (A.ID=B.ID)
GROUP BY (A.ID)
)T
ON A.ID = T.ID
SELECT A.ID,A.FREEDATE,T.C_SUM
FROM A,(
SELECT A.ID ID,MAX(B.SUM) C_SUM
FROM B,A
WHERE (B.OPTDATE < A.FREEDATE) AND (A.ID=B.ID)
GROUP BY (A.ID)
)T
WHERE A.ID = T.ID
with a as
(select 1 id, '20090921' freedate from dual),
b as
(select 1 id,'20091001' optdate,100 sums from dual
union all
select 1,'20090901',200 from dual
union all
select 1,'20090801',200 from dual)
select a.id,sum(sums) from a,b
where a.id=b.id
and a.freedate>b.optdate
group by a.id