where子句中包含大小写+并集+like的未知列

需要时,不能在where条件下使用别名(可以在order by和group by中的某些mysql版本中使用别名),必须重复代码

  select reqId,sDate,stitle,sType,status,reqUser, assignedTo from 
  (select request_id as reqId,date as sDate,title as sTitle,type as sType,status as status,request_user as reqUser ,
  case when status='posted' then fbuser.name
       when status='awaiting approval' then fbmanager.name
       when status='approved' then fbAdmin.name
       when status='rejected' then fbAdmin.name
  else null
  end as assignedTo
  from fb_requests fr
  left join users fbuser on (fr.reqUser = fbuser.id)
  left join users fbmanager on (fr.managerid= fbmanager.id)
  left join users fbAdmin on (fr.adminin = fbAdmin.id)
  where 1=1
  and ( upper(
        case when status='posted' then fbuser.name
           when status='awaiting approval' then fbmanager.name
           when status='approved' then fbAdmin.name
           when status='rejected' then fbAdmin.name
      else null
      end
  ) like upper('abc') )
  UNION
  select request_id as reqId,date as sDate,title as sTitle,type as sType,status as status,request_user as reqUser ,
  case when status='posted' then fbuser.name
       when status='awaiting approval' then fbmanager.name
  else null
  end as assignedTo
  from fb_wall_posts fw
  left join users fbuser on (fw.request_by_id = fbuser.id)
  left join users fbmanager on (fw.manager_id= fbmanager.id)
   where 1=1
  and ( upper(
    case when status='posted' then fbuser.name
       when status='awaiting approval' then fbmanager.name
      else null
    end
    ) like upper('abc') ))

当where子句执行时,尚未定义AssignedTo,因此您必须使用完整的case语句,因为别名尚未创建。

select reqId,sDate,stitle,sType,status,reqUser,assignedTo from 
(select request_id as reqId,date as sDate,title as sTitle,type as sType,status as status,request_user as reqUser ,
case when status='posted' then fbuser.name
     when status='awaiting approval' then fbmanager.name
     when status='approved' then fbAdmin.name
     when status='rejected' then fbAdmin.name
else null
end as assignedTo
from fb_requests fr
left join users fbuser on (fr.reqUser = fbuser.id)
left join users fbmanager on (fr.managerid= fbmanager.id)
left join users fbAdmin on (fr.adminin = fbAdmin.id)
where 1=1
and ( upper(case when status='posted' then fbuser.name
     when status='awaiting approval' then fbmanager.name
     when status='approved' then fbAdmin.name
   ) like upper('abc') )
UNION
select request_id as reqId,date as sDate,title as sTitle,type as sType,status as status,request_user as reqUser ,
case when status='posted' then fbuser.name
     when status='awaiting approval' then fbmanager.name
else null
end as assignedTo
from fb_wall_posts fw
left join users fbuser on (fw.request_by_id = fbuser.id)
left join users fbmanager on (fw.manager_id= fbmanager.id)
 where 1=1
and ( upper(case when status='posted' then fbuser.name
     when status='awaiting approval' then fbmanager.name
     when status='approved' then fbAdmin.name
   ) like upper('abc') ))