create table test
(bh VARCHAR2(30),/*经销商代码*/
yxq_q,VARCHAR2(8),/*有效起*/
yxq_q,VARCHAR2(8),/*有效止*/
insert into test values('001','2011-08-01','2011-12-30')
insert into test values('002','2011-08-01','2011-08-31')
insert into test values('002','2011-09-01','2011-09-30')
insert into test values('003','2011-08-01','2011-08-31')
insert into test values('003','2011-09-01','2011-09-30')
insert into test values('003','2011-10-01','2011-10-31')
需要显示(连续>=3个月)数据:
bh yyyy-mm
001 2011-08
001 2011-09
001 2011-10
001 2011-11
003 2011-12
003 2011-08
003 2011-09
003 2011-10如何查询,请高手指教?
(bh VARCHAR2(30),/*经销商代码*/
yxq_q,VARCHAR2(8),/*有效起*/
yxq_q,VARCHAR2(8),/*有效止*/
insert into test values('001','2011-08-01','2011-12-30')
insert into test values('002','2011-08-01','2011-08-31')
insert into test values('002','2011-09-01','2011-09-30')
insert into test values('003','2011-08-01','2011-08-31')
insert into test values('003','2011-09-01','2011-09-30')
insert into test values('003','2011-10-01','2011-10-31')
需要显示(连续>=3个月)数据:
bh yyyy-mm
001 2011-08
001 2011-09
001 2011-10
001 2011-11
003 2011-12
003 2011-08
003 2011-09
003 2011-10如何查询,请高手指教?
有效起止会存在非1号或月末情况吗?这时候算一个月吗?
001的12月不显示是因为没到31号?顺便提下:yxq_q,VARCHAR2(8)这个定义执行
insert into test values('001','2011-08-01','2011-12-30')
会报错吧
001 2011-08 001 2011-09
001 2011-10
001 2011-11
001 2011-12
create table test
(bh varchar2(30),
yxq_q1 varchar2(10),
yxq_q2 varchar2(10));insert into test values('001','2011-08-01','2011-12-30');
insert into test values('002','2011-08-01','2011-08-31');
insert into test values('002','2011-09-01','2011-09-30');
insert into test values('003','2011-08-01','2011-08-31');
insert into test values('003','2011-09-01','2011-09-30');
insert into test values('003','2011-10-01','2011-10-31');select tt.bh,dd.rn
from
(select t.bh,substr(t.yxq_q1,6,2) m1,substr(t.yxq_q2,6,2) m2
from (select bh,min(yxq_q1) yxq_q1,max(yxq_q2) yxq_q2
from test group by bh) t
where add_months(to_date(yxq_q1,'yyyy-mm-dd'),3)<=to_date(yxq_q2,'yyyy-mm-dd')+1) tt,
(select rownum rn from dual connect by rownum <=12) dd
where dd.rn between tt.m1 and tt.m2
order by tt.bh,dd.rn;BH RN
------------------------------ ----------
001 8
001 9
001 10
001 11
001 12
003 8
003 9
003 10
--刚不小心把年份删了
create table test
(bh varchar2(30),
yxq_q1 varchar2(10),
yxq_q2 varchar2(10));insert into test values('001','2011-08-01','2011-12-30');
insert into test values('002','2011-08-01','2011-08-31');
insert into test values('002','2011-09-01','2011-09-30');
insert into test values('003','2011-08-01','2011-08-31');
insert into test values('003','2011-09-01','2011-09-30');
insert into test values('003','2011-10-01','2011-10-31');select tt.bh,dd.rn
from
(select t.bh,substr(t.yxq_q1,1,7) m1,substr(t.yxq_q2,1,7) m2
from (select bh,min(yxq_q1) yxq_q1,max(yxq_q2) yxq_q2
from test group by bh) t
where add_months(to_date(yxq_q1,'yyyy-mm-dd'),3)<=to_date(yxq_q2,'yyyy-mm-dd')+1) tt,
(select '2011-'||lpad(rownum,2,'0') rn from dual connect by rownum <=12) dd
where dd.rn between tt.m1 and tt.m2
order by tt.bh,dd.rn;BH RN
------------------------------ ---------
001 2011-08
001 2011-09
001 2011-10
001 2011-11
001 2011-12
003 2011-08
003 2011-09
003 2011-10
--
select t.bh,substr(t.yxq_q1,1,7) m1,substr(t.yxq_q2,1,7) m2
from (select bh,min(yxq_q1) yxq_q1,max(yxq_q2) yxq_q2
from test group by bh) t
where add_months(to_date(yxq_q1,'yyyy-mm-dd'),3)<=to_date(yxq_q2,'yyyy-mm-dd')+1;BH M1 M2
------------------------------ -------------- --------------
001 2011-08 2011-12
003 2011-08 2011-10至少这段不会出错,再把月份显示的条件调一下就可以了
with tt as (select t.bh,d.dd
from (select bh,substr(yxq_q1,1,7) m1,substr(yxq_q2,1,7) m2 from test) t,
(select y.yyyy||'-'||m.mm dd
from (select lpad(rownum,2,'0') mm from dual connect by rownum <=12) m,
(select distinct substr(YXQ_Q1,1,4) yyyy from test union select distinct substr(YXQ_Q2,1,4) from test) y) d
where d.dd between t.m1 and t.m2)
select tt1.bh,tt1.dd
from tt tt1
where exists(select 1 from tt tt2 where tt1.bh=tt2.bh group by tt2.bh having count(tt2.dd)>=3);