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

方法一：
【初始代码】

select t1.id, t1.is_group_buy, c.name as client_name from
(select id, is_group_buy, client_id from order_info
where user_id in 
(select user_id from order_info
where date > '2025-10-15'
 and product_name in ('C++','Python','Java')
and status = 'completed'
group by user_id
having count(*) >=2)) as t1
left join 
client as c
on t1.client_id = c.id
order by t1.id

出错：多输出了几行
原因：在筛选id时只设置了user_id的限制条件，但是没有限制日期、产品、完成状态的限制，因此筛选出来的是满足条件的user_id的所有订单。
【修改后代码】

select t1.id, t1.is_group_buy, c.name as client_name from
(select id, is_group_buy, client_id from order_info
where user_id in 
(select user_id from order_info
where date > '2025-10-15'
and product_name in ('C++','Python','Java')
and status = 'completed'
group by user_id
having count(*) >=2)
and date > '2025-10-15'  #关键就是增加这几行关于订单的限制
and product_name in ('C++','Python','Java')
and status = 'completed') as t1
left join 
client as c
on t1.client_id = c.id
order by t1.id

方法二：

select t.id, t.is_group_buy, c.name as client_name from
(select t1.id, t1.is_group_buy, t1.client_id from 
(select *, count(*) over(partition by user_id) as cnt from order_info
where date > '2025-10-15'
and product_name in ('C++','Python','Java')
and status = 'completed') as t1
where cnt >=2) as t
left join client as c
on c.id = t.client_id
order by t.id

代码的复杂度下降了。