思路一:
1.生成count_times表 用来记录用户id出现的次数(times)
2.将 order_info表 left join count_times表 删选出需要的行 作为tmp2表
3.将 client 表 left join tmp2 表 生成最终答案
代码:
select tmp2.id,tmp2.is_group_buy,client.name client_name
// ③将 client 表 left join tmp2 表 生成最终答案
from client right join
// ②将 order_info表 left join count_times表 删选出需要的行 作为tmp2表
(select order_info.id,order_info.client_id,order_info.is_group_buy
from order_info left join
// ①生成count_times表
(select user_id,count(product_name) times
from order_info
group by user_id) as count_times
on order_info.user_id = count_times.user_id
where date>'2025-10-15'
and status='completed'
and product_name in('C++','Java','Python')
and count_times.times>1) as tmp2
on client.id = tmp2.client_id
提交出错
思路二:
看题解用开窗函数
1.用开窗函数添加times列到order_info表中 left join client表 得到结果作为tmp表
2.对tmp表的数据进行筛选
代码:
select id,is_group_buy,name client_name
from
(select a.*,count(a.id) over (partition by a.user_id) times,b.name
from order_info a left join client b
on b.id = a.client_id) as tmp
where tmp.date>'2025-10-15'
and tmp.status='completed'
and tmp.product_name in('C++','Java','Python')
and tmp.times>1
order by id asc
提交报错
正确题解
思路:
1.用开窗函数给order_info表添加一列 筛选后 作为t2表
2.client表 right join t2表
select t2.id,t2.is_group_buy,t1.name as client_name
from client t1 right join
(
select *,count(id) over(partition by user_id) as number
from order_info
where datediff(date,"2025-10-15")>0
and status="completed"
and product_name in ("C++","Java","Python")
) t2
on t1.id=t2.client_id
where t2.number >1
order by t2.id
和我的区别就是先筛选了结果之后再和client表join
不知道我的错误出在哪里 求指导