mysql - 带 case + union + like 的 where 子句中的未知列

Question

我正在mysql中进行sql查询。我的查询是这样的，

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(assignedTo) 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(assignedTo) like upper('abc') ))

这里我得到了 'unknowncolumnsignedTo' 。谁能帮我解决这个问题。预先感谢您的宝贵回复。

Answer 1

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

  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') ))