表结构,数据如下:-- 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;
需求:求缺失<间断>的日期:
如下数据,
bxbh ksny zzny
1 199501 199612
1 199701 199702
1 199703 199703
1 199704 199704
1 199706 199706
1 199707 199707
2 199701 199707
2 199708 199708
2 199709 199709
2 199710 199712
要得到的结果:1 199705 199705
2 199706 199706
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;
需求:求缺失<间断>的日期:
如下数据,
bxbh ksny zzny
1 199501 199612
1 199701 199702
1 199703 199703
1 199704 199704
1 199706 199706
1 199707 199707
2 199701 199707
2 199708 199708
2 199709 199709
2 199710 199712
要得到的结果:1 199705 199705
2 199706 199706
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);思路大致如此
要得到什么结果
为什么得到的是
1 199705 199705
2 199706 199706 没明白
(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'));
-------------------- -------------------- --------------------
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
楼上的可以,不错
order by rownum最好改成order by ksny更好一些