select * from t where trunc(time,'mm')=trunc(sysdate,'mm') union all select * from t where trunc(time,'mm')=trunc(add_months(sysdate,-1),'mm') union all select * from t where trunc(time,'mm')=trunc(add_months(sysdate,-2),'mm') ;
最好按年份月份来查询,避免把去年、前年的相同月份数据检索出来。 select * from t where trunc(time,'yyyymm')=trunc(sysdate,'yyyymm') union all select * from t where trunc(time,'yyyymm')=trunc(add_months(sysdate,-1),'yyyymm') union all select * from t where trunc(time,'yyyymm')=trunc(add_months(sysdate,-2),'yyyymm') ; 另外估计数据量较大,最好考虑索引。
下面方法也可以: select * from t where months_between(trunc(sysdate),trunc(coldate)) <3 order by coldate 如果月份是不是连续的,如当月、前第2个月,前第4个月 select * from t where months_between(trunc(sysdate),trunc(coldate)) in (0,2,4) order by coldate
不好意思,上面的漏了点东西: select * from t where months_between(trunc(sysdate,'mm'),trunc(coldate,'mm')) <3 order by coldate 如果月份是不是连续的,如当月、前第2个月,前第4个月 select * from t where months_between(trunc(sysdate,'mm'),trunc(coldate,'mm')) in (0,2,4) order by coldate另外trunc(sysdate-92,'yyyymm')是不正确的写法(ORA-01898: 精确度说明符过多),trunc(sysdate,'mm')和trunc(sysdate,'month')都可以
union all
select * from t where trunc(time,'mm')=trunc(add_months(sysdate,-1),'mm')
union all
select * from t where trunc(time,'mm')=trunc(add_months(sysdate,-2),'mm')
;
select * from t where trunc(time,'yyyymm')=trunc(sysdate,'yyyymm')
union all
select * from t where trunc(time,'yyyymm')=trunc(add_months(sysdate,-1),'yyyymm')
union all
select * from t where trunc(time,'yyyymm')=trunc(add_months(sysdate,-2),'yyyymm')
;
另外估计数据量较大,最好考虑索引。
select * from t where months_between(trunc(sysdate),trunc(coldate)) <3
order by coldate
如果月份是不是连续的,如当月、前第2个月,前第4个月
select * from t where months_between(trunc(sysdate),trunc(coldate)) in (0,2,4)
order by coldate
select * from t where months_between(trunc(sysdate,'mm'),trunc(coldate,'mm')) <3
order by coldate
如果月份是不是连续的,如当月、前第2个月,前第4个月
select * from t where months_between(trunc(sysdate,'mm'),trunc(coldate,'mm')) in (0,2,4)
order by coldate另外trunc(sysdate-92,'yyyymm')是不正确的写法(ORA-01898: 精确度说明符过多),trunc(sysdate,'mm')和trunc(sysdate,'month')都可以