select B.* from A,B where A.af1=B.bf1 and abs(to_number(to_char(A.af2,'YYYY'))-to_number(to_char(A.af2,'YYYY'))) between 0 and 10
天 select count(*) from B where exists ( select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)>0 and abs(b.bf2-a.af2)<10)月 select count(*) from B where exists ( select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)/30>0 and abs(b.bf2-a.af2)/30<10)年 select count(*) from B where exists ( select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)/30*12>0 and abs(b.bf2-a.af2)/30*12<10)有一点误差
取日期的年可以用:extract(year from bf2) - extract(month from af2),extract返回的是数字型,可以直接减相差月份用:months_between(bf2,bf1)相差天数直接日期减就可以了,bf2-bf1
不好意思,上面写错了点,相差年应该是:extract(year from bf2) - extract(year from af2)
A,B
where A.af1=B.bf1
and abs(to_number(to_char(A.af2,'YYYY'))-to_number(to_char(A.af2,'YYYY')))
between 0 and 10
select count(*) from B where exists (
select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)>0 and abs(b.bf2-a.af2)<10)月
select count(*) from B where exists (
select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)/30>0 and abs(b.bf2-a.af2)/30<10)年
select count(*) from B where exists (
select 1 from a where a.af1=b.bf1 and abs(b.bf2-a.af2)/30*12>0 and abs(b.bf2-a.af2)/30*12<10)有一点误差
abs(to_number(to_char(A.af2,'YYYY'))-to_number(to_char(A.af2,'YYYY')))
改为:
abs(to_number(to_char(A.af2,'YYYY'))-to_number(to_char(B.bf2,'YYYY')))