php - 如何在带有左连接的 mysql select 查询中使用 CASE？

Question

select  r.email,
        rc.cuisine_id,
        rc.restaurant_id, 
        c.id as c_id ,
        c.name as cus_name,
        r.image,
        r.web_address,
        r.area,
        rp.TypeId,  
        rp.PaymentStatus , 
        rp.RestaurantId 
from restaurant as r
left join restaurant_cuisine as rc on r.id=rc.restaurant_id
left join cuisine as c on rc.cuisine_id=c.id  where c.status='1'

需要的案例陈述

CASE WHEN rp.TypeId = '1' 
     THEN left join restaurant_payment as rp on r.id=rp.RestaurantId 
     WHEN rp.TypeId = '3'
     THEN left join users as u on rp.RestaurantId=u.id
END

Answer 1

试试这个

select  r.email,
        rc.cuisine_id,
        rc.restaurant_id, 
        c.id as c_id ,
        c.name as cus_name,
        r.image,
        r.web_address,
        r.area,
        rp.TypeId,  
        rp.PaymentStatus , 
        rp.RestaurantId 
from restaurant as r
left join restaurant_cuisine as rc on r.id=rc.restaurant_id
left join cuisine as c on rc.cuisine_id=c.id  where c.status='1' and
rp.TypeId = CASE 
                 WHEN rp.TypeId = '1' 
                 THEN left join restaurant_payment as rp on r.id=rp.RestaurantId 
                 WHEN rp.TypeId = '3'
                 THEN left join users as u on rp.RestaurantId=u.id
            END