Oracle學習筆記整理之日期查詢篇

感覺在第一步和第二步的格式轉化有點兒複雜了，還請各位大師多多指教，感謝感謝....

今天接到一個需求，剛開始毫無頭緒，慢慢的梳理了半個小時，最終用下面的方式實現了，感覺不夠完善，所以貼出來，請大家指導一下，有沒有更好、更高效的辦法，也為自己以後遇到類似的需求有個參考。

需求：現有表1（test_1）、表2（test_2）,表1為錄入表，表2為退費表，需要查詢出表1中的日期不存在與表2中的資料，如表1有資料2014-01-01至2014-12-01，
表2中有資料2014-02-01至2014-03-01和2014-05-01至2014-05-01，
那麼最終應得到資料為2014-01-01至2014-01-01、2014-04-01至2014-04-01、2014-06-01至2014-12-01。

建立測試表：
create table test_1(
nian varchar2(4),
qrq date,
zrq date
);

create table test_2(
nian varchar2(4),
qrq date,
zrq date
);

插入測試資料：
insert into test_1 values('2013',date'2013-01-01',date'2013-03-01');
insert into test_1 values('2013',date'2013-05-01',date'2013-06-01');
insert into test_1 values('2013',date'2013-07-01',date'2013-10-01');
insert into test_1 values('2013',date'2013-12-01',date'2013-12-01');
insert into test_1 values('2014',date'2014-01-01',date'2014-05-01');
insert into test_1 values('2014',date'2014-06-01',date'2014-06-01');
insert into test_1 values('2014',date'2014-07-01',date'2014-07-01');
insert into test_2 values('2013',date'2013-09-01',date'2013-09-01');
insert into test_2 values('2014',date'2014-04-01',date'2014-04-01');
commit;



原資料：表1：





原資料：表2：





實現查詢：
第一步：實現表1 的資料格式轉換
select f.nian, f.qrq, f.zrq, d.yrq
  from (select add_months(c.qrq, level - 1) yrq
           from (select b.qrq, months_between(b.zrq, b.qrq) + 1 ys
                    from (select min(a.qrq) qrq, max(a.zrq) zrq from test_1 a) b) c
         connect by level <= c.ys) d,
       (select e.nian, e.qrq, e.zrq from test_1 e) f
where d.yrq between f.qrq and f.zrq;



第二步：實現表2的資料格式轉換
select d.yrq
  from (select add_months(c.qrq, level - 1) yrq
           from (select b.qrq, months_between(b.zrq, b.qrq) + 1 ys
                    from (select min(a.qrq) qrq, max(a.zrq) zrq from test_2 a) b) c
         connect by level <= c.ys) d, (select e.qrq, e.zrq from test_2 e) f
where d.yrq between f.qrq and f.zrq;



第三步：去除存在於退費表2中的日期資料（組合第一步和第二步的語句）

select f.nian, f.qrq, f.zrq, d.yrq
  from (select add_months(c.qrq, level - 1) yrq
           from (select b.qrq, months_between(b.zrq, b.qrq) + 1 ys
                    from (select min(a.qrq) qrq, max(a.zrq) zrq from test_1 a) b) c
         connect by level <= c.ys) d,
       (select e.nian, e.qrq, e.zrq from test_1 e) f
where d.yrq between f.qrq and f.zrq
   and not exists
(select j.yrq
          from (select add_months(i.qrq, level - 1) yrq
                   from (select h.qrq, months_between(h.zrq, h.qrq) + 1 ys
                            from (select min(g.qrq) qrq, max(g.zrq) zrq
                                     from test_2 g) h) i
                 connect by level <= i.ys) j,
               (select k.qrq, k.zrq from test_2 k) l
         where j.yrq between l.qrq and l.zrq
           and j.yrq = d.yrq);





第四步：重新設定分組值

select m.nian, m.qrq, m.zrq, m.yrq,
       add_months(m.yrq,
                   -dense_rank()
                    over(partition by m.nian, m.qrq, m.zrq order by m.yrq)) yrqg
  from (select f.nian, f.qrq, f.zrq, d.yrq
           from (select add_months(c.qrq, level - 1) yrq
                    from (select b.qrq, months_between(b.zrq, b.qrq) + 1 ys
                             from (select min(a.qrq) qrq, max(a.zrq) zrq
                                      from test_1 a) b) c
                  connect by level <= c.ys) d,
                (select e.nian, e.qrq, e.zrq from test_1 e) f
          where d.yrq between f.qrq and f.zrq
            and not exists
          (select j.yrq
                   from (select add_months(i.qrq, level - 1) yrq
                            from (select h.qrq,
                                          months_between(h.zrq, h.qrq) + 1 ys
                                     from (select min(g.qrq) qrq, max(g.zrq) zrq
                                              from test_2 g) h) i
                          connect by level <= i.ys) j,
                        (select k.qrq, k.zrq from test_2 k) l
                  where j.yrq between l.qrq and l.zrq
                    and j.yrq = d.yrq)) m;


第五步：實現我們預期的結果

select n.nian, min(n.yrq) qrq, max(n.yrq) zrq
  from (select m.nian, m.qrq, m.zrq, m.yrq,
                add_months(m.yrq,
                            -dense_rank() over(partition by m.nian, m.qrq,
                                  m.zrq order by m.yrq)) yrqg
           from (select f.nian, f.qrq, f.zrq, d.yrq
                    from (select add_months(c.qrq, level - 1) yrq
                             from (select b.qrq,
                                           months_between(b.zrq, b.qrq) + 1 ys
                                      from (select min(a.qrq) qrq, max(a.zrq) zrq
                                               from test_1 a) b) c
                           connect by level <= c.ys) d,
                         (select e.nian, e.qrq, e.zrq from test_1 e) f
                   where d.yrq between f.qrq and f.zrq
                     and not exists
                   (select j.yrq
                            from (select add_months(i.qrq, level - 1) yrq
                                     from (select h.qrq,
                                                   months_between(h.zrq, h.qrq) + 1 ys
                                              from (select min(g.qrq) qrq,
                                                            max(g.zrq) zrq
                                                       from test_2 g) h) i
                                   connect by level <= i.ys) j,
                                 (select k.qrq, k.zrq from test_2 k) l
                           where j.yrq between l.qrq and l.zrq
                             and j.yrq = d.yrq)) m) n
group by n.nian, n.yrqg;

這是我在專案中最終使用的SQL語句：

如果直接在原表上遞迴，效率奇低，使用with後沒有問題。
gr_yljf_zyd_jxzhmx_lr表中有1037762資料；
gr_yljf_zyd_jxxx表中有65468資料；
gr_yljf_zyd_jxzhtf表中有34595資料；


with t1 as
(select lr.grid, lr.cwid, lr.xzqh, lr.nian, lr.qrq, lr.zrq
    from gr_yljf_zyd_jxzhmx_lr lr, gr_yljf_zyd_jxxx jx
   where lr.cwid = jx.cwid
     and lr.grid = jx.grid
     and jx.shzt = '0'
     and lr.grid =
         (select dp_gr_query.get_grid('410901196802242730') from dual)
     and lr.yjfjs != 0.00
     and lr.xzqh not like '11%'
     and to_char(jx.sprq, 'yyyy.mm') <=
         to_char(add_months(sysdate, -1), 'yyyy.mm')),
t2 as
(select tf.grid, tf.cwid, tf.nian, tf.qrq, tf.zrq
    from gr_yljf_zyd_jxzhtf tf, gr_yljf_zyd_jxxx jx
   where tf.grid = jx.grid(+)
     and jx.shzt = '0'
     and tf.grid =
         (select dp_gr_query.get_grid('410901196802242730') from dual))
select lb.grid, lb.xzqh, lb.cwid, lb.nian, min(lb.yrq) qrq, max(lb.yrq) zrq
  from (select la.grid, la.xzqh, la.cwid, la.nian, la.qrq, la.zrq, la.yrq,
                add_months(la.yrq,
                            -dense_rank()
                             over(partition by la.grid, la.xzqh, la.cwid, la.nian,
                                  la.qrq, la.zrq order by la.yrq)) yrqg
           from (select t3.grid, t3.cwid, t3.xzqh, t3.nian, t3.qrq, t3.zrq,
                         t3.yrq
                    from (select t1.grid, t1.cwid, t1.xzqh, t1.nian, t1.qrq,
                                  t1.zrq, add_months(t1.qrq, level - 1) yrq
                             from t1
                           connect by LEVEL <= months_between(t1.zrq, t1.qrq) + 1
                                  AND PRIOR t1.qrq = t1.qrq
                                  AND PRIOR t1.zrq = t1.zrq
                                  AND PRIOR dbms_random.value IS NOT NULL) t3
                   where not exists
                   (select 1
                            from (select add_months(t2.qrq, level - 1) yrq
                                     from t2
                                   connect by LEVEL <=
                                              months_between(t2.zrq, t2.qrq) + 1
                                          AND PRIOR t2.qrq = t2.qrq
                                          AND PRIOR t2.zrq = t2.zrq
                                          AND PRIOR dbms_random.value IS NOT NULL) t4
                           where t3.yrq = t4.yrq)) la) lb
group by lb.grid, lb.xzqh, lb.cwid, lb.nian, lb.qrq, lb.zrq;