牛客的课程订单分析(六)
牛客的课程订单分析(六)
http://www.nowcoder.com/questionTerminal/c5736983c322483e9f269dd23bdf2f6f
这个题目其实是牛客的课程订单分析(三)的另外一个联立表和左连接的考察
根据 https://blog.nowcoder.net/n/7a090c43899e47f38e75c67be7133b4f 我们可以写出
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')
那么要得到客户端的信息，我们左连接一下client，因为有可能存在拼团，导致client_id为0，不能不输出，要输出NULL，就必须左连接，那么代码就如下:
select table_a.id,is_group_buy,client.name as 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
order by id;
