题解 | #牛客的课程订单分析(七)#
牛客的课程订单分析(七)
http://www.nowcoder.com/practice/d6f4a37f966145da8900ba9edcc4c068
- 查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的用户id
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
- 查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的 所有信息
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(*)>=2
)
and date>'2025-10-15'
and status ='completed'
and product_name in ('C++','Java','Python')
- 将2作为新表和client表连接， 通过group by和case when 得到结果
select (case when name is null then 'GroupBuy' else name end) as source ,count(*)
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(*)>=2
)
and date>'2025-10-15'
and status ='completed'
and product_name in ('C++','Java','Python')
) order_new
left join client c
on order_new.client_id = c.id
group by (case when name is null then 'GroupBuy' else name end)
order by source asc
