牛客的课程订单分析(七)
牛客的课程订单分析(七)
http://www.nowcoder.com/questionTerminal/d6f4a37f966145da8900ba9edcc4c068
select table_a.*,client.name from (select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')) table_a left join client on table_a.client_id=client.id来获取所有的信息,然后就是将client_id与is_group_buy双聚合,再通过is_group_buy来判断,如下:
select if(is_group_buy='Yes','GroupBuy',name) as source,count(is_group_buy) as cnt from (select table_a.*,client.name from (select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')) table_a left join client on table_a.client_id=client.id )first_filter group by client_id,is_group_buy order by source ;