题解 | #牛客的课程订单分析(三)#

第一种解法
1.找出满足题目要求的user_id;
2.按照题目要求 select 出全部字段；
3.uesr_id in(1 中 select 出的 user_id);

select * 
from order_info
where date>'2025-10-15'
and product_name in('C++','Java','Python')
and status ='completed'
and 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(*)>=2)

第二种解法
1.先满足题目要求 select 出 user_id,然后用 with as 临时存储为一个视图；
2.和1一样的条件，只不过现在不用分组聚合，user_id 条件为 select * from a;
with as 在后期需要反复用到临时视图时，可大大降低代码量，以及提高可读性，本题的作用有限

with  a  as
(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(*)>=2)
select * 
from order_info
where date>'2025-10-15'
and product_name in('C++','Java','Python')
and status ='completed'
and user_id in (select * from a)
order by id asc