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

SELECT IF(a.client_id = 0,'GroupBuy',c.name) AS source,COUNT(*) AS cnt
  FROM 
(SELECT client_id
  FROM order_info 
 WHERE date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++','Java','Python')
       AND user_id IN (SELECT user_id
                         FROM order_info
                       WHERE status = 'completed' AND status = 'completed' AND product_name IN ('C++','Java','Python')
                        GROUP BY user_id 
                       HAVING COUNT(*) >= 2 ) ) a
  LEFT JOIN client c ON a.client_id = c.id
 GROUP BY a.client_id
 ORDER BY source

找到符合要求的user_id

user_id IN (SELECT user_id
                         FROM order_info
                       WHERE status = 'completed' AND status = 'completed' AND product_name IN ('C++','Java','Python')
                        GROUP BY user_id 
                       HAVING COUNT(*) >= 2 )

对每个user_id对应的客户端id进行分类计数

 SELECT IF(a.client_id = 0,'GroupBuy',c.name) AS source,COUNT(*) AS cnt
 GROUP BY a.client_id