SQL 查询 - 跨多个字段搜索 [英] SQL Query - Search across multiple fields

问题描述

我正在尝试实施一项搜索，您可以在其中输入多个搜索词以形成 AND 条件.还应该在数据库的不同字段中进行搜索.

I'm trying to implement a search, where you can enter more than one searchterm to form an AND-Condition. Also it should search in different fields of the database.

例如:
当您输入 "Bill Seattle" 时，您应该得到一个记录，其中 NAME 与 Bill 匹配并且 CITY 与 CITY 匹配em>西雅图.您不应该得到任何仅 CITY 匹配 Seattle 的行.

So for instance:
You when you enter "Bill Seattle", you should get a record where NAME matches Bill and CITY matches Seattle. You shoudn't get any rows where just the CITY matches Seattle.

现在，这听起来并不复杂，但它似乎真的比我想象的要难.我想出了这样的东西:

Now, this doesn't sound to complicated, but it really seems to be harder than I thought. I've come up with something like this:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (NAME LIKE 'Seattle%')) 
AND
((CITY LIKE 'Bill%') OR (CITY LIKE 'Seattle%'))

这在我们之前的例子中效果很好，但假设我们添加了另一个字段:

This works well in our previous case, but suppose we add another field:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (NAME LIKE 'Seattle%')) 
AND
((CITY LIKE 'Bill%') OR (CITY LIKE 'Seattle%'))
AND
((COMPANY LIKE 'Bill%') OR (COMPANY LIKE 'Seattle%'))

现在它不会返回想要的结果.我本质上需要(我猜)是一种确定子条件是否像

Now it won't return the desired result. What I essentialy need (I guess) is a way to determine if a sub-condition like

 ((COMPANY LIKE 'Bill%') OR (COMPANY LIKE 'Seattle%'))

返回一些东西，如果没有，我需要忽略那个条件.

returns something and if not, i need to ignore that condition.

顺便说一句，我使用的是 MSSQL '05.

Btw, I'm using MSSQL '05.

推荐答案

首先，我不知道它在 MSSQL 上是怎么做的，但你可能想看看全文搜索.

First of all, I don't know how it's done on MSSQL, but you might want to look at fulltext search.

另一方面，这可能是您真正的意思吗:

On the other hand, could this be what you really mean:

SELECT * FROM ADDRESS WHERE
((NAME LIKE 'Bill%') OR (CITY LIKE 'Bill%') OR (COMPANY LIKE 'Bill%'))
AND
((NAME LIKE 'Seattle%') OR (CITY LIKE 'Seattle%') OR (COMPANY LIKE 'Seattle%'))

这篇关于SQL 查询 - 跨多个字段搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！