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

#法1：用ifnull来写
select ifnull(c.name,"GroupBuy") source,count(*) cnt
from  (
select *,count(*)over(partition by user_id)cnt
from order_info
where date>2025-10-25
and status="completed"
and product_name in("C++","Python","Java"))t left join client c
on c.id=t.client_id
where  cnt>=2
group by source
order by source

#法2：用case when
select case when t.is_group_buy="YES" then "GroupBuy"
    else c.name end source, count(*) cnt
from  (
select *,count(*)over(partition by user_id)cnt
from order_info
where date>2025-10-25
and status="completed"
and product_name in("C++","Python","Java"))t left join client c
on c.id=t.client_id
where  cnt>=2
group by source
order by source