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

1 子查询中的左连接保证购买课程的人终端可以与client中一一对应，没有的会自动被赋值为空

2 分组排出买了至少两门课程的客户的user_id然后判断

select id,
    case
        when is_group_buy='Yes' then 'Yes'
        else 'No'
    end
    as is_group_buy,
    client_name
from(
    select a.id,user_id,is_group_buy,name client_name
    from order_info a left join client b on a.client_id=b.id
    where status='completed' and (product_name='C++' or product_name='Java' or product_name='Python')
)c
where user_id in (
    select user_id
    from (
        select a.id,user_id,is_group_buy,name client_name
        from order_info a left join client b on a.client_id=b.id
        where status='completed' and (product_name='C++' or product_name='Java' or product_name='Python')
    )d
    group by user_id
    having count(user_id)>=2
)
order by id asc