2020-03-27
阅读量:
818
有如下表和字段
订单表：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
1
2
关注作者
收藏
评论(0)
发表评论
暂无数据
推荐帖子
零点溯灵
2025-02-16
0条评论
零点溯灵
2025-02-16
1条评论
CDA持证人阿涛哥
2024-12-02
0条评论