id---username---registered_timeorder订单表结构
order_id----user_id---order_time
select user_id
from user a,order b
where a.id=b.user_id and a.registered_time<'2014-03-01' and b.order_time<'2014-04-01'
group by user_id
having count(*) between 1 and 3;
order_id----user_id---order_time
select user_id
from user a,order b
where a.id=b.user_id and a.registered_time<'2014-03-01' and b.order_time<'2014-04-01'
group by user_id
having count(*) between 1 and 3;
select a.id,a.username
from user a
inner join order b
on a.id = b.user_id
where a.registered_time<'2014-03-01' and b.order_time<'2014-04-01'
group by user_id
having count(*) between 1 and 3
and count(case when b.order_time >= '2014-02-01' and b.order_time <= '2014-03-31' then 0 END)/count(*)>=0.5;2、select count(*) from user a
inner join order b
on a.id = b.user_id
where a.registered_time <= '2014-03-01'
and b.order_time between '2014-04-01' and '2014-04-30'
你没有提供脚本,我这边也懒得建表模拟数据运行,不过应该符合你要求
第一个,3月1日之前注册,4月1日前有过1到3个订单,并且在大多数订单在2月1日和3月31日之间。
第一个,3月1日之前注册,4月1日前有过1到3个订单
select *
from user
where registered_time<3月1日
and (select count(*) from order where user_id=user.id and order_time<4月1日) between 1 and 3
where exists (select 1 from order where user_id=user.id and order_time between 4月1日 and 4月30日)