感觉在第一步和第二步的格式转化有点儿复杂了,还请各位大师多多指教,感谢感谢....
今天接到一个需求,刚开始毫无头绪,慢慢的梳理了半个小时,最终用下面的方式实现了,感觉不够完善,所以贴出来,请大家指导一下,有没有更好、更高效的办法,也为自己以后遇到类似的需求有个参考。
需求:现有表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;
今天接到一个需求,刚开始毫无头绪,慢慢的梳理了半个小时,最终用下面的方式实现了,感觉不够完善,所以贴出来,请大家指导一下,有没有更好、更高效的办法,也为自己以后遇到类似的需求有个参考。
需求:现有表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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25357621/viewspace-1217039/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25357621/viewspace-1217039/