2020-03-27
阅读量:
819
有如下表和字段
订单表:order,字段:order_id,user_id,city_id,create_time
请写一条sql语句,做一个报表,包含如下字段:城市id,最近7天内下过单的用户数,首次下单时间在最近7天的用户数
select a.city_id,最近7天内下过单的用户数,首次下单时间在最近7天的用户数
from
(select city_id,count(distinct u_id) 最近7天内下过单的用户数
from orders
where create_time>subdate(now(),interval 7 day)
group by city_id) a
join
(select city_id,sum(首次下单时间>subdate(now(),interval 7 day)) 首次下单时间在最近7天的用户数
from
(select city_id,u_id,min(create_time) 首次下单时间
from orders
group by city_id,u_id
having min(create_time)) t
group by city_id) b
on a.city_id=b.city_id;






评论(0)


暂无数据
推荐帖子
0条评论
1条评论
0条评论