牛客的课程订单分析(三)
牛客的课程订单分析(三)
http://www.nowcoder.com/questionTerminal/4ae8cff2505f4d7cb68fb0ec7cf80c57
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
查找同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，那么利用子查询，写出: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)
就可以获得符合以上条件的user_id所有的购买信息，可能从例子中得到是如下信息:
所有，我们还要排除掉1，3的信息，所以，最后的语句如下:
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;
还有一些同学比较高级的写法:
WITH vip AS(
SELECT user_id
FROM order_info
WHERE date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
GROUP BY user_id
HAVING COUNT(id) >= 2
)
SELECT *
FROM order_info
WHERE date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++', 'Java', 'Python')
AND user_id IN (SELECT * FROM vip)
ORDER BY id;
阿里巴巴公司氛围 441人发布
投递美团等公司10个岗位 >