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

• 参考答案

select oi.id,oi.is_group_buy,if(oi.is_group_buy='No',c.name,null)
from order_info oi left join client c 
on oi.client_id = c.id
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 o
    where date>'2025-10-15'
     and status='completed' 
     and product_name in ('C++','Java','Python')
    group by user_id
    having count(o.id)>1) 
order by oi.id

• 答案解析
  我在做这道题目的时候写出了下面的代码

select oi.id,oi.is_group_buy,if(oi.is_group_buy='No',c.name,null)
from order_info oi left join client c
on oi.client_id = c.id
where  date > '2025-10-15'
and status = 'completed'
and product_name IN ('C++','Java','Python')
group by oi.user_id
having(product_name)>=2
order by oi.id

这样得出的结果只有:
4|No|IOS
5|Yes|None
为什么会这样呢?相信很多同学也会遇到这样简单的错误...且听我慢慢道来
原因就出在这句:
group by oi.user_id
因为聚合了,将我们查询的结果按照user_id聚到了一行里,所以查询结果里只给我们显示了4,5,而没有6,7,其实6,7我们上面的代码也查出来了,被聚合了而已

• 那么我们为了把全部结果显示出来,我们这里就不能使用聚合函数
  我们把这两句

group by oi.user_id
having(product_name)>=2

替换成:

and user_id in
    (select user_id from order_info o
    where date>'2025-10-15'
     and status='completed'
     and product_name in ('C++','Java','Python')
    group by user_id
    having count(o.id)>1)

就ok了