牛客的课程订单分析(六)

简介

from 接 子查询
count(1) 统计个数
over (partition by user_id) 以user_id 进行分区
left join on 后面还可以加上where

select a.id, a.is_group_buy, b.name as client_name
from
(
    select id, client_id, is_group_buy, count(1) over(partition by user_id) as num
    from order_info
    where product_name in ('C++', 'Python', 'Java') and
    status = 'completed' and
    date > '2025-10-15'
) as a
left join 
client as b
on a.client_id = b.id
where a.num >= 2
order by a.id;