题解 | #查找描述信息中包含robot的电影对应的分类名称以及电影数目,而且还需要该分类对应电影数量>=5部#
牛客的课程订单分析(三)
http://www.nowcoder.com/practice/4ae8cff2505f4d7cb68fb0ec7cf80c57
select * from order_info where datediff(date,"2025-10-15")>0 #日期在2025-10-15之后 and status ="completed" and product_name in ("C++","Java","Python") and user_id in (select user_id from order_info where datediff(date,"2025-10-15")>0 #日期在2025-10-15之后 and status ="completed" and product_name in ("C++","Java","Python") group by user_id having count(user_id)>= 2) order by id
方法二 窗口函数
select a.id,a.user_id,a.product_name,a.status,a.client_id,a.date from (select *,count(id) over(partition by user_id) as cnt from order_info where datediff(date,"2025-10-15")>0 #日期在2025-10-15之后 and status ="completed" and product_name in ("C++","Java","Python") ) as a where cnt >= 2 #不能放到子查询中 因为子查询中where 在select 之前执行 cnt还没定义 order by a.id