使用over开窗函数就可以了 例子: with a as (select '单位A' as t_code from dual), b as (select '单位A' as t_code,'2016-'||lpad(level,2,'0') as t_months ,level as t_amount from dual connect by level <=12) select a.*,b.t_months,sum(t_amount)over(partition by a.t_code,b.t_months) as 当月投资,sum(t_amount)over(partition by a.t_code,substr(b.t_months,1,4)) as 当年投资 from a left join b on b.t_code=a.t_code
例子:
with a as
(select '单位A' as t_code from dual),
b as
(select '单位A' as t_code,'2016-'||lpad(level,2,'0') as t_months ,level as t_amount from dual connect by level <=12)
select a.*,b.t_months,sum(t_amount)over(partition by a.t_code,b.t_months) as 当月投资,sum(t_amount)over(partition by a.t_code,substr(b.t_months,1,4)) as 当年投资 from a
left join b
on b.t_code=a.t_code