with t as (select 2003 year,2 sum from dual union all select 2004 year,3 sum from dual union all select 2005 year,12 sum from dual union all select 2010 year,22 sum from dual) select t2.year,sum from (select start_year+level-1 year from (select min(year)start_year,max(year)end_year from t) t1 connect by level<=end_year-start_year+1)t2,t where t2.year=t.year(+) order by year;
楼上的方法是最简单的, 再给楼主提供一个稍微复杂的: create table t(year varchar2(10),sum number) insert into t values('1999','1'); insert into t values('2003','2'); insert into t values('2004','3'); insert into t values('2005','4'); insert into t values('2010','5');select * from (select year,sum from t union all select year - level || '' year, sum from (select year, lag(year, 1) over(order by year) + 1 s, year - 1 e, lag(sum) over(order by year) sum from t) where e - s = 0 or e - s > 1 connect by prior year = year and level <= e - s + 1 and prior dbms_random.value is not null) order by 1;
t as (select 2003 year,2 sum from dual union all
select 2004 year,3 sum from dual union all
select 2005 year,12 sum from dual union all
select 2010 year,22 sum from dual)
select t2.year,sum
from (select start_year+level-1 year
from (select min(year)start_year,max(year)end_year from t) t1
connect by level<=end_year-start_year+1)t2,t
where t2.year=t.year(+)
order by year;
再给楼主提供一个稍微复杂的:
create table t(year varchar2(10),sum number)
insert into t values('1999','1');
insert into t values('2003','2');
insert into t values('2004','3');
insert into t values('2005','4');
insert into t values('2010','5');select *
from (select year,sum
from t
union all
select year - level || '' year, sum
from (select year,
lag(year, 1) over(order by year) + 1 s,
year - 1 e,
lag(sum) over(order by year) sum
from t)
where e - s = 0
or e - s > 1
connect by prior year = year
and level <= e - s + 1
and prior dbms_random.value is not null)
order by 1;