如何在SQL查询中使用AND搜索多个术语

Question

我有一个简单的web应用程序，在可搜索的表中列出不同的数据类型。

我使用MySQL作为数据库。

例如，有一个具有FirstName、LastName等的Employees列表

示例Employees表：

ID  | FIRSTNAME | LASTNAME     | EMAILADDRESS
---------------------------------------------
001 | John      | Smith        | [e-mail]
002 | John      | Jones        | [e-mail]
003 | Michael   | Smith        | [e-mail]
004 | Betty     | Taylor-Smith | [e-mail]

有一个用于搜索的简单文本字段。

搜索John Smith的结果是任何包含John和Smith的内容：

001 | John      | Smith

搜索John，结果是其中包含John的任何内容：

001 | John      | Smith
002 | John      | Jones

搜索Smith，结果是其中包含Smith的任何内容：

001 | John      | Smith
003 | Michael   | Smith
004 | Betty     | Taylor-Smith

所以。。。我的问题是。。。我应该如何为此编写SQL查询？

select * from employees where ...

如果我搜索John Smith。。。

((firstname like "%John%") OR (firstname like "%Smith%")) 
AND 
((lastname like "%John%") OR (lastname like "%Smith%"));
AND 
((emailaddress like "%John%") OR (emailaddress like "%Smith%"));

由于emailaddress不匹配，返回零结果。

如果我把AND改成OR。。。

((firstname like "%John%") OR (firstname like "%Smith%")) 
OR 
((lastname like "%John%") OR (lastname like "%Smith%"));
OR 
((emailaddress like "%John%") OR (emailaddress like "%Smith%"));

然后我得到所有四个结果，因为每行都有一个John或Smith。。。这不是我想要的结果，我只想要第一行001。

001 | John      | Smith        | [e-mail]
002 | John      | Jones        | [e-mail]
003 | Michael   | Smith        | [e-mail]
003 | Betty     | Taylor-Smith | [e-mail]

我应该如何处理此SQL查询？

Answer 1

看起来你想要：

((firstname LIKE "%John%") OR (lastname LIKE "%John%") OR (emailaddress LIKE "%John%")) 
AND 
((firstname LIKE "%Smith%") OR (lastname LIKE "%Smith%") OR (emailaddress LIKE "%Smith%")) 

Answer 2

您是否可能使用CONCAT_WS来满足您的需求？

SELECT *
FROM employees
WHERE CONCAT_WS(" ", firstname, lastname) LIKE "%John Smith%"
OR firstname LIKE "%John Smith%" # in case user provides just a first name
OR lastname LIKE "%John Smith%" # in case user provides just a last name
OR emailaddress LIKE "%John Smith%" # support searching by e-mail at the same time