建个中间表:create table table_91 (start_year number(4),end_year number(4));
insert into table_91 values('2013','2020');
create table table_92 (year number(4),count number(5));
insert into table_92 values('2015','1000');
insert into table_92 values('2018','3000');
insert into table_92 values('2019','4000');
create table table_93(year number(4),count number(6));declare
start_year1 number(4):=0;
end_year1 number(4):=0;
m_count number(2);
begin
select start_year into start_year1 from table_91;
select end_year into end_year1 from table_91;
loop
select count(*) into m_count from table_92 where year =start_year1;
if m_count = 0 then
insert into table_93 values(start_year1,0);
else
insert into table_93 select * from table_92 where year =start_year1 ;
end if;
start_year1:=start_year1+1;
if start_year1 = end_year1+1 then
exit;
end if;
end loop;
commit;
end;select * from table_93;YEAR COUNT
2013 0
2014 0
2015 1000
2016 0
2017 0
2018 3000
2019 4000
2020 0
insert into table_91 values('2013','2020');
create table table_92 (year number(4),count number(5));
insert into table_92 values('2015','1000');
insert into table_92 values('2018','3000');
insert into table_92 values('2019','4000');
create table table_93(year number(4),count number(6));declare
start_year1 number(4):=0;
end_year1 number(4):=0;
m_count number(2);
begin
select start_year into start_year1 from table_91;
select end_year into end_year1 from table_91;
loop
select count(*) into m_count from table_92 where year =start_year1;
if m_count = 0 then
insert into table_93 values(start_year1,0);
else
insert into table_93 select * from table_92 where year =start_year1 ;
end if;
start_year1:=start_year1+1;
if start_year1 = end_year1+1 then
exit;
end if;
end loop;
commit;
end;select * from table_93;YEAR COUNT
2013 0
2014 0
2015 1000
2016 0
2017 0
2018 3000
2019 4000
2020 0
解决方案 »
- 宿舍表和用户表怎么建立关联
- 提高dbms_job.submit的执行效率(跪求高手指教)
- 一个简单的sql语句执行结果的困惑
- 我建立啦一个函数 包含动态的sql 但运行时有问题 高手帮忙
- 查询一个表的字段信息?
- 怪事,为什么在VB中调用sqlserver的存储过程参数下标是从1开始,可调用oracle的是从0开始?
- oracle9i安装问题?
- 急.ORACLE存储过程的问题!!!!
- [100分]有谁的Oracle Developer 6i能够web发布正常的,进来看看吧,一个死都解决不了的问题.
- 客户端通过防火墙后不能连接到服务器,怎么办?来讨论者有分!
- ---c# 怎么读出 和 写入 Oracle里 long 类型的字段???
- 关于oracle读写os文件!请帮忙!
with tableA as
(
select 2013 c1,2017 c2 from dual
),tableB as
(
select 2013 c3,1000 c4 from dual union all
select 2014 c3,2000 c4 from dual union all
select 2016 c3,3000 c4 from dual
)select a.c1,nvl(b.c4,0) c4
from
(
select c1+level-1 c1
from tableA
connect by level <= c2-c1+1
) a left join tableB b on a.c1 = b.c3
order by a.c1 c1 c4
-------------------------
1 2013 1000
2 2014 2000
3 2015 0
4 2016 3000
5 2017 0
用lag or lead分析函数