【Mysql】sql语句查询在2025-10-15以后，同一个用户下。。。

题目描述：sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序。
个人思路：与（二）几乎一样。不过的是因为要显示的所有信息因此不能直接使用group by后的结果。
方法一：内表找出user_id，外表找出该user_id符合的记录。

with moreThan1 as 
(
    select user_id
    from order_info
    where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status ="completed"
    group by user_id
    having count(id)>1
)

select *
from order_info
where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status="completed"
      and user_id in (select * from moreThan1)
order by id;

或者这么写

select *
from order_info
where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status="completed"
      and user_id in (
                      select user_id
                      from order_info
                      where datediff(date,"2025-10-15")>0
                          and product_name in ("C++","Java","Python")
                          and status ="completed"
                      group by user_id
                      having count(id)>1
                     )
order by id;

方法二：窗口函数

select t1.id, t1.user_id,t1.product_name,t1.status,t1.client_id,t1.date
from
(
    select *,count(id) over(partition by user_id) as number
    from order_info
    where datediff(date,"2025-10-15")>0
      and status ="completed"
      and product_name in ("C++","Java","Python")
) t1
where t1.number >1
order by t1.id

用count()作为窗口函数来检索以user_id分组的行数并作为临时表的新列。然后对临时表按题目要求依次添加筛选条件。关于为什么t1.number要放在外表where后进行判断。因为count()属于聚合函数在临时表t1里无法放在where后进行判断。但是在外表number是一个列的字段名，其值可以直接拿来进行判断。但更主要的原因还是mysql的执行顺序：开始->from子句->where子句->group by子句->having子句->select子句->最终结果。