mysql - mysql中where子句中的CASE语句

我正在尝试从在WHERE子句中使用CASE条件的表中获取数据，当前正在使用以下查询：

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ",
implode(", ", $aColumns))." FROM (select CASE when r.agent_id=24
THEN r.Unit ELSE '--' END AS MyUnit,
CASE when r.agent_id=24 THEN r.landlord_name ELSE '--' END AS landlord_name_new,
r.*,l.loc_name as location,sl.sub_sub_loc as sub_location,
c.category as   category,CONCAT(u.first_name, ' ', u.last_name) As agent
from crm_sales r
LEFT JOIN crm_location l ON r.area_location_id=l.loc_id
LEFT JOIN crm_subloc sl ON sl.sub_loc_id = r.sub_area_location_id
LEFT JOIN crm_category c on c.id = r.category_id
LEFT JOIN crm_users u on u.id=r.agent_id
where r.is_active=1 AND r.is_archive=0

AND CASE agent_id WHEN r.agent_id!=24 then r.status=2 else 1=1

 group by r.ref) sel
$sWhere
$sOrder
$sLimit
";

现在，我想再添加一个条件，像这样。

IF（r.agent_id！= 24）然后r.status = 2

编辑：添加我想但错误的情况

最佳答案

将WHERE子句中的case / when子句修复为...

AND CASE WHEN r.agent_id != 24
         then r.status = 2
         else 1 = 1 end

关于mysql - mysql中where子句中的CASE语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26886541/

Case

mysql - mysql中where子句中的CASE语句