题目要求

查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序，以上例子查询结果如下:

思路一：用子查询 计算每个用户的order数量，用where筛选 order数量>=2

结果不行。因为子查询有多行

select user_id
from order_info
where 
(
    select 
        count(distinct id) as order_num
    from order_info
    group by user_id
) >= 2
and date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Java','Python')
group by user_id

思路二：用窗口函数，计算每个用户 在符合条件下 有多少订单。在原表后新增一列「订单数」，再筛选

select 
    distinct o.user_id
from order_info o
    left join (
	  		# 计算每个用户在符合条件状态下的 订单数
            select
                distinct user_id,
                count(id)over(partition by user_id) as order_num
            from order_info
            where date > '2025-10-15'
                and status = 'completed'
                and product_name in ('C++','Java','Python')
        ) n
    on o.user_id = n.user_id
where n.order_num >=2
order by o.user_id asc 

思路三：case when 订单数>=2，则记为yes。在原表后新增一列「是否2个以上订单」，再筛选

注：与窗口函数，思路基本相同，只是具体列不一样

select 
    distinct o.user_id
from order_info o
    left join (
            select
                distinct user_id,
                case
                    when count(id) >=2 then 'yes'
                    else 'no'
                end as order_two
	  			# 把窗口函数 换成 case when 
            from order_info
            where  date > '2025-10-15'
                and status = 'completed'
                and product_name in ('C++','Java','Python')
            group by user_id
	  		# 这里需要有group by 
        ) n
    on o.user_id = n.user_id
where n.order_two = 'yes'
# 按照case when 的列进行筛选
order by o.user_id asc