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

思路：在上一题中的使用case的题解中再增加一个分组和count即可。

case也可以被 ifnull(c.name, 'GroupBuy') 函数替换。

完整代码：

select 
	case when o.is_group_buy = 'Yes' then 'GroupBuy'
	when o.is_group_buy='No' then name end as source,
	count(*) as cnt
from order_info o
left join client c
on o.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
    where date > '2025-10-15'
    and status = 'completed'
    and product_name IN ('C++','Java','Python')
    group by user_id
    having count(status) > 1
)
group by source
order by source

使用ifnull函数：

select 
	ifnull(c.name, 'GroupBuy') as source,
	count(*) as cnt
from order_info o
left join client c
on o.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
    where date > '2025-10-15'
    and status = 'completed'
    and product_name IN ('C++','Java','Python')
    group by user_id
    having count(status) > 1
)
group by source
order by source