原题链接
步骤一：根据条件需要找出来源信息，找出满足条件的user_id查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序,如下
select
user_id
from order_info
where status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by user_id
having count(*) > 1
步骤二：将选择的user_id作为条件，从原表中筛选出属于来源信息部分，并根据client_id分组计数
select
client_id,
count(*) as cnt
from order_info
where user_id in (
select
user_id
from order_info
where status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by user_id
having count(*) > 1
)
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by client_id
步骤三：将步骤二的结果与client表left join，并采用ifnull函数将null值设置为“GrouBuy”，即最终结果
select
finull(c.name, 'GroupBuy') as source,
t.cnt
from (
select
client_id,
count(*) as cnt
from order_info
where user_id in (
select
user_id
from order_info
where status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by user_id
having count(*) > 1
)
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by client_id
) as t
left join client as c
on t.client_id = c.id
order by source
思路来自题解区第二个答案
