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

思路：本题的重点是通过是否是拼团订单来判断是否显示客户端名字

使用 left join 解决，以 order_info, t2 连接后的表为主表，左连接 client 表即可解决，或者使用 case when xxx then end 的方法

      case when is_group_buy = 'Yes' then 'None' -- 编译器必须要为None才识别为Null
      when is_group_buy='No' then name end client_name 

来枚举，最后解决显示客户端名字。

也可以使用 ifnull(c.name, 'None') 来替换。

完整代码 (不使用 case)：

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

完整代码 (使用 case)：

select o.id, is_group_buy,
	case when is_group_buy = 'Yes' then 'None'
	when is_group_buy='No' then name end client_name 
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) >= 2
)
order by o.id