题解 | #牛客的课程订单分析(四)#
牛客的课程订单分析(四)
http://www.nowcoder.com/practice/c93d2079282f4943a3771ca6fd081c23
这题感觉有点问题。 1.查询在2025-10-15以后； 2.用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程； 3.输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date； 4.以及所有日期里购买成功的C++课程或Java课程或Python课程的次数cnt！！！ 关于这一条我看大多数答主的答案意思都是只筛了2025-10-15之后的而不是所有日期的总购买次数，但这样子答案才能通过，就很迷； 5.并且输出结果按照user_id升序排序；
可以通过的答案： select user_id, min(date) as first_buy_date, count(*) as cnt from order_info where datediff(date,"2025-10-15")>0 and status="completed" and product_name in ("C++","Java","Python") group by user_id having count(user_id)>1 order by user_id
考虑第4条需求的答案： --查询每个用户的首次成功购买日期 select user_id, min(date) as first_buy_date, from order_info where datediff(date,"2025-10-15")>0 and status="completed" and product_name in ("C++","Java","Python") group by user_id having count(user_id)>1
--查询每个用户所有日期内成功购买这三门课至少一门的总购买次数 select user_id, count(*) as cnt, from order_info where status="completed" and product_name in ("C++","Java","Python") group by user_id
--合并这两个结果集输出 select a.user_id,a.first_by_date,b.cnt from (select user_id, min(date) as first_buy_date, from order_info where datediff(date,"2025-10-15")>0 and status="completed" and product_name in ("C++","Java","Python") group by user_id having count(user_id)>1) a join (select user_id, count(*) as cnt, from order_info where status="completed" and product_name in ("C++","Java","Python") group by user_id) b on a.user_id=b.user_id;
