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

法一：先统计指定日期后所有的订单数，然后再选其中订单数大于等于2的客户。

SELECT user_id FROM 
(SELECT user_id,COUNT(IF(status='completed',1,NULL)) num FROM order_info 
WHERE product_name IN ('C++','Java','Python') and date>'2025-10-15' GROUP BY user_id) a
WHERE num>=2
ORDER BY user_id;

法二：和法一思路一样，只是不用count(if())语句。

SELECT user_id FROM 
(SELECT user_id,COUNT(*) num FROM order_info 
WHERE product_name IN ('C++','Java','Python') and date>'2025-10-15' and status='completed' 
GROUP BY user_id) a
WHERE num>=2
ORDER BY user_id;

法三：用having函数。group by先分组，同时提取满足having后面条件的组。

SELECT user_id 
FROM order_info
WHERE product_name IN ('C++','Java','Python') 
and date>'2025-10-15' and status='completed' 
GROUP BY user_id
HAVING COUNT(*)>=2
ORDER BY user_id;