mysql——case

  

查询在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