3,490
社区成员
发帖
与我相关
我的任务
分享
-- Create table
create table TEXT_IMPORT
(
BXBH VARCHAR2(20),
KSNY VARCHAR2(20),
ZZNY VARCHAR2(20)
);
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199501', '199612');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199701', '199702');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199703', '199703');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199704', '199704');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199706', '199706');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('1', '199707', '199707');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('2', '199701', '199707');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('2', '199708', '199708');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('2', '199709', '199709');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('2', '199710', '199712');
insert into ESCHS.TEXT_IMPORT (BXBH, KSNY, ZZNY)
values ('', '', '');
commit;
09:11:31 scott@TUNGKONG> select * from text_import;
BXBH KSNY ZZNY
-------------------- -------------------- --------------------
1 199501 199612
1 199701 199702
1 199703 199703
1 199704 199704
1 199706 199706
1 199707 199707
2 199701 199705
2 199707 199708
2 199709 199709
2 199710 199712
已选择11行。
已用时间: 00: 00: 00.01
09:11:52 scott@TUNGKONG> select bxbh,to_char(add_months(to_date(zzny,'yyyymm'),1),'yyyymm') ksny,to_char(add_months(to_date(ny,'yyyymm'),-1),'yyyymm') zzny from
09:12:17 2 (select * from (select bxbh,ksny,zzny,lead(ksny) over(partition by bxbh order by rownum) ny from text_import)
09:12:17 3 where add_months(to_date(zzny,'yyyymm'),1) <> to_date(ny,'yyyymm'));
BXBH KSNY ZZNY
-------------------- ------ ------
1 199705 199705
2 199706 199706
已用时间: 00: 00: 00.01
select bxbh,to_char(add_months(to_date(zzny,'yyyymm'),1),'yyyymm') ksny,to_char(add_months(to_date(ny,'yyyymm'),-1),'yyyymm') zzny from
(select * from (select bxbh,ksny,zzny,lead(ksny) over(partition by bxbh order by rownum) ny from text_import)
where add_months(to_date(zzny,'yyyymm'),1) <> to_date(ny,'yyyymm'));
select t.rn
from
(select add_months(to_date('199501','yyyymm'), rownum - 1) rn
from dual
where rownum <= 24) t
where not exists (select 1 from TEXT_IMPORT t1 where t.rn between t1.KSNY and t1.ZZNY);