查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的课程的订单id,是否拼团以及客户端名字信息,最后一列如果是非拼团订单,则显示对应客户端名字,如果是拼团订单,则显示NULL,并且按照order_info的id升序排序,以上例子查询结果如下:
select o.id,o.is_group_buy, client.name as client_name
from (
select * from order_info
where date >'2025-10-15' and status='completed'
and user_id in (
select user_id from order_info
where date>'2025-10-15' and status='completed'
group by user_id
having count(user_id)>1
)
)o
left join client
on o.client_id = client.id
order by o.id
select o.id,o.is_group_buy,
case when o.is_group_buy = 'No' then c.name else NUll end
from order_info o left join client c on o.client_id = c.id
where o.date > '2025-10-15'
and o.status = 'completed'
and user_id in(
select user_id from order_info
where date >'2025-10-15'
and status = 'completed'
group by user_id
having count(user_id)>1
) order by o.id