订单表中包含信息如下,查询每个用户在首次付费后 30 天内的总付费次数
解题思路
#1.每个用户的首次付费时间和首次付费的30天后
select users_id,
min(`time`) as 首次付费时间,
adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order
group by users_id;
#2.左连接
select o.*,t.首次付费后30天
from trade_order o
left join
(select users_id,min(`time`) as 首次付费时间,adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order
group by users_id)t
on o.users_id =t.users_id;
#3.进行分组计数和时间过滤
select o.users_id ,count(*) as 总次数
from trade_order o
left join
(select users_id,min(`time`) as 首次付费时间,adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order
group by users_id)t
on o.users_id =t.users_id
where o.`time` <= t.首次付费后30天
group by o.users_id;
输出结果: