原题链接
步骤一:根据条件需要找出来源信息,找出满足条件的user_id查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息,第一列是显示的是客户端名字,如果是拼团订单则显示GroupBuy,第二列显示这个客户端(或者是拼团订单)有多少订单,最后结果按照第一列(source)升序排序,如下
select user_id from order_info where status = 'completed' and product_name in ('C++', 'Java', 'Python') and date > '2025-10-15' group by user_id having count(*) > 1步骤二:将选择的user_id作为条件,从原表中筛选出属于来源信息部分,并根据client_id分组计数
select client_id, count(*) as cnt from order_info where user_id in ( select user_id from order_info where status = 'completed' and product_name in ('C++', 'Java', 'Python') and date > '2025-10-15' group by user_id having count(*) > 1 ) and status = 'completed' and product_name in ('C++', 'Java', 'Python') and date > '2025-10-15' group by client_id步骤三:将步骤二的结果与client表left join,并采用ifnull函数将null值设置为“GrouBuy”,即最终结果
select finull(c.name, 'GroupBuy') as source, t.cnt from ( select client_id, count(*) as cnt from order_info where user_id in ( select user_id from order_info where status = 'completed' and product_name in ('C++', 'Java', 'Python') and date > '2025-10-15' group by user_id having count(*) > 1 ) and status = 'completed' and product_name in ('C++', 'Java', 'Python') and date > '2025-10-15' group by client_id ) as t left join client as c on t.client_id = c.id order by source思路来自题解区第二个答案
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)