牛客的课程订单分析(四)题解
牛客的课程订单分析(四)
http://www.nowcoder.com/questionTerminal/c93d2079282f4943a3771ca6fd081c23
select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id
可以将以上查询的结果当成一个新的表，那么这样思考就简单很多了，把这个新的表，按照user_id聚合，并且取出这种情况的最小购买时间，以及count(user_id)，就能得到正确结果，如下:
select user_id,min(date) as first_buy_date,count(user_id) as cnt from
(select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id) table_a
group by user_id order by user_id;
其实这个题目订单(二)和订单(三)筛选的东西，聚合之后是一样的，所以根据订单(二)的写法，加聚合更简单，:
select user_id,min(date) first_buy_date,count(user_id) cnt
from order_info
where date > '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(user_id) > 1
order by user_id
腾讯成长空间 5950人发布