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

方法一：窗口函数
select a.id,a.user_id,a.product_name,a.status,a.client_id,a.date from 
(select *,
case when (count(user_id)over(partition by user_id))>1 then 1
else 0 end ct from order_info where date >'2025-10-15' and status='completed' and product_name in ('C++','Python','Java')) a
where a.ct=1
order by a.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++','Python','Java')
group by user_id having count(user_id)>1)
and date >'2025-10-15' and status='completed' and product_name in ('C++','Python','Java')
order by id