在MySQL的HAVING和WHERE子句之间使用"OR"? [英] Using 'OR' between HAVING and WHERE clause in MySQL?

问题描述

我正在尝试使用一个简单的已提交字段在MySQL中获取记录.更准确地说，用户输入名称(名字或姓氏或全名)，服务器应返回匹配的行.

I am trying to fetch records in MySQL using a simple used submitted field. More precisely, the user inputs a name (firstname or lastname or fullname) and the server should return matched rows.

到目前为止，我正在做的事情是这样的:

What I am doing so far is something like:

SELECT * FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%'

目前效果不错，但是(显然)当用户提交全名时，该效果不起作用.有没有办法在整个"WHERE类型条件"和"HAVING类型条件"之间添加OR?这样，我可以做类似的事情:

That works well for now, but that (obviously) won't work when a user submits the fullname. Is there a way to add a OR between the whole 'WHERE type conditions' and the 'HAVING type conditions'? This way I could do something like:

SELECT [some fields], CONCAT(firstname, ' ', 'lastname') as fullname 
FROM people 
WHERE 
   firstname LIKE '%user_submitted_data%' OR 
   lastname LIKE '%user_submitted_data%' OR 
   HAVING fullname LIKE '%user_submitted_data%'

我知道我可以拆分原始字符串，但这会产生一些负面影响，因为您必须处理包含诸如"De Gaule"之类的空格的名称.

I know I could just split the original string but that has some negative impact since you have to deal with names containing spaces such as 'De Gaule' and stuff like that.

推荐答案

执行子查询:

SELECT [some fields]
FROM
  SELECT firstname, lastname, CONCAT(firstname, ' ', lastname) as fullname
  FROM people) AS tmp
WHERE firstname LIKE '%user_submitted_data%'
OR lastname LIKE '%user_submitted_data%'
OR fullname LIKE '%user_submitted_data%'

这篇关于在MySQL的HAVING和WHERE子句之间使用"OR"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！