题解 | #牛客的课程订单分析(四)#
牛客的课程订单分析(六)
http://www.nowcoder.com/practice/c5736983c322483e9f269dd23bdf2f6f
此题用case when 表示name,null
注意表联系需要外连接,保留order_info这一主表,根据位置选择左连接或者右链接
选用窗口函数的好处是可以选择多个字段,如果用count(*)聚合函数,则不可以选择多个字段
SELECT o.id, is_group_buy,
(case when o.client_id = c.id then name else null end) as client_name from client c
right join
(select *,count(*)over(partition by user_id) cnt from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++','Python','Java')
) o
on o.client_id = c.id
where o.cnt > 1
ORDER by o.id asc