我有一个简单的Web应用程序,在可搜索表中列出了不同的数据类型。
我正在使用MySQL作为数据库。
例如,有一个具有FirstName,LastName等的Employees列表。
示例Employees
表:
ID | FIRSTNAME | LASTNAME | EMAILADDRESS
---------------------------------------------
001 | John | Smith | [email protected]
002 | John | Jones | [email protected]
003 | Michael | Smith | [email protected]
004 | Betty | Taylor-Smith | [email protected]
有一个用于搜索的简单文本字段。
搜索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 | [email protected]
002 | John | Jones | [email protected]
003 | Michael | Smith | [email protected]
003 | Betty | Taylor-Smith | [email protected]
我应该如何处理此SQL查询?
分析解答
好像您想要的:
((firstname LIKE "%John%") OR (lastname LIKE "%John%") OR (emailaddress LIKE "%John%"))
AND
((firstname LIKE "%Smith%") OR (lastname LIKE "%Smith%") OR (emailaddress LIKE "%Smith%"))