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

Select中case两种写法用法

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END 
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

举例：

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; 
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

Select中IF用法举例

SELECT IF(1>2,2,3); -> 3

本题解答

select o.id, o.is_group_buy ,
#case when o.is_group_buy = 'No'then c.name else NULL end
if(o.is_group_buy = 'No',c.name,NULL)
from order_info as o left join client as c
on o.client_id = c.id
where user_id in
(select user_id
from order_info
where date >'2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
group by user_id
having count(id) > 1)
#因为子查询筛选出来的user_id有重复的不满足题目条件，所以需要再筛选
and o.date > '2025-10-15'
and o.product_name in ('C++','Java','Python')
and o.status = 'completed'
order by o.id asc