select 用户姓名,编号,max(消费时间) from 表 where 消费时间 between to_date('20090101','yyyymmdd') and to_date('20090630','yyyymmdd')+0.99999 group by 用户姓名,编号 having max(消费时间)<to_date('20090401','yyyymmdd')
select 姓名 from tbl where 消费时间>=trunc(add_months(sysdate,-6)) and not exists ( select * from tbl t2 where 消费时间>=trunc(add_months(sysdate,-3)) and tb1.姓名=t2.姓名)
select 用户姓名,编号,max(消费时间) from 表 where (EXTRACT(year FROM 消费时间) - EXTRACT(year FROM to_date('2009-04-30','yyyy-mm-dd'))) * 12 + EXTRACT(month FROM 消费时间) - EXTRACT(month FROM to_date('2009-04-30','yyyy-mm-dd')) between 4 and 6 group by 用户姓名,编号
select tb1.* from tbl where 消费时间>=trunc(add_months(sysdate,-6)) and not exists ( select 1 from tbl t2 where 消费时间>=trunc(add_months(sysdate,-3)) and tb1.姓名=t2.姓名)
from 表
where 消费时间 between to_date('20090101','yyyymmdd') and to_date('20090630','yyyymmdd')+0.99999
group by 用户姓名,编号
having max(消费时间)<to_date('20090401','yyyymmdd')
from tbl where
消费时间>=trunc(add_months(sysdate,-6)) and not exists ( select * from tbl t2 where 消费时间>=trunc(add_months(sysdate,-3)) and
tb1.姓名=t2.姓名)
from 表
where (EXTRACT(year FROM 消费时间) - EXTRACT(year FROM to_date('2009-04-30','yyyy-mm-dd'))) * 12 +
EXTRACT(month FROM 消费时间) - EXTRACT(month FROM to_date('2009-04-30','yyyy-mm-dd'))
between 4 and 6
group by 用户姓名,编号
from tbl where
消费时间>=trunc(add_months(sysdate,-6)) and not exists ( select 1 from tbl t2 where 消费时间>=trunc(add_months(sysdate,-3)) and
tb1.姓名=t2.姓名)