mysql - 在联接表上搜索全名

我写了一个查询，以在联接表上按全名搜索，但出现此错误。任何想法？

SELECT wrs.id, wrs.fname, wrs.sname, wrs.email1, wrs.applied, wrs.firstlogin, wrs.tel1,
       SUBSTR( wrs.lastlogin, -10 ) AS last_login,
       CONCAT_WS(  ' ', wrs.fname, wrs.sname ) AS full_name, wqs.level,
       wqs.subject, wqs.university, wqs.dissertation
FROM temp_users AS wrs
    LEFT JOIN writer_qualifications AS wqs ON wqs.writer_id = wrs.id
WHERE wrs.fname LIKE  '%micky%'
   OR wrs.sname LIKE  '%micky%'
   OR full_name LIKE  '%micky%'
   AND wrs.status =  '1'
GROUP BY wrs.id


MySQL said: Documentation

#1054 - Unknown column 'full_name' in 'where clause'

最佳答案

您不能在WHERE子句中使用诸如full_name之类的别名，请参见manual


  标准SQL不允许在WHERE子句中引用列别名。
  施加此限制是因为当WHERE子句是
  评估后，可能尚未确定列值。


因此您必须在WHERE子句中使用表达式CONCAT_WS( ' ', wrs.fname, wrs.sname )而不是别名full_name：

SELECT wrs.id, wrs.fname, wrs.sname, wrs.email1, wrs.applied, wrs.firstlogin, wrs.tel1,
   SUBSTR( wrs.lastlogin, -10 ) AS last_login,
   CONCAT_WS(  ' ', wrs.fname, wrs.sname ) AS full_name, wqs.level,
   wqs.subject, wqs.university, wqs.dissertation
FROM temp_users AS wrs
    LEFT JOIN writer_qualifications AS wqs ON wqs.writer_id = wrs.id
WHERE wrs.fname LIKE  '%micky%'
    OR wrs.sname LIKE  '%micky%'
    OR CONCAT_WS(  ' ', wrs.fname, wrs.sname ) LIKE  '%micky%'
    AND wrs.status =  '1'
GROUP BY wrs.id

关于mysql - 在联接表上搜索全名，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23500026/