热线电话:13121318867

登录
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;

3.4423
2
关注作者
收藏
评论(0)

发表评论

暂无数据
推荐帖子