SQL 查询 - 多个关键字搜索多列
我正在寻找一种使用 Coldfusion 搜索具有多个关键字的 sql 表中的多列的方法。我目前有以下代码，其中一半有效。
<cfquery name="rsSearchEmployees" datasource="#request.database#">
SELECT idn, firstname, lastname
FROM Employees
WHERE (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">firstname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
OR (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">lastname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
ORDER BY firstname ASC
它使用文本框中输入的关键字搜索 2 列：名字和姓氏。使用 cfloop 标签对关键字进行细分。
此代码确实会生成结果，但当前如果在一列或两列中未找到 2 个或更多关键字，则不会删除结果。
例如，假设我想搜索 Steve Smith，当我输入 Steve 时，它会显示 Steve 的行。史密斯的情况也是如此。如果我输入 Steve Smith，它仍然显示正确的行。
当我输入类似 Steve Jobs 的内容时，就会出错。输入此内容仍然会显示史蒂夫·史密斯，而它不应该显示。有谁知道如何解决这个问题？
感谢您提前提供的任何帮助。 克里斯
I am looking for a way to search multiple columns in a sql table with multiple keywords using Coldfusion. I currently have the following code which half works.
<cfquery name="rsSearchEmployees" datasource="#request.database#">
SELECT idn, firstname, lastname
FROM Employees
WHERE (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">firstname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
OR (<cfloop list="#arguments.nameSearchString#" delimiters=" " index="word">lastname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#Trim(word)#%" /> OR </cfloop> (1 <> 1))
ORDER BY firstname ASC
It searches 2 columns, firstname and lastname with keywords entered into a textbox. The keywords are broken down using the cfloop tag.
This code does produce results, but currently does not remove results if 2 or more keywords are not found in one or both columns.
For example, say i wanted to search for Steve Smith, when i type in Steve it shows the row for Steve. The same happens for Smith. If i type in Steve Smith it still shows the row which is correct.
It goes wrong when i type in something like Steve Jobs. Typing this in still shows Steve Smith when it shouldnt. Does anyone have a clue how to get around this?
Thanks for any help in advance.
Chris
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
克里斯，
我认为你问题的核心是你的逻辑目前是说“显示名字或姓氏与搜索字符串中的一个单词匹配的所有记录”。您允许某人将“Matthew Mark Luke John”作为字符串进行搜索，以找到具有任何这些名字的人，而不是仅将搜索词视为单个名字。
这会导致由于仅匹配名字而导致 search='Steve Smith' 出现记录“Steve Jobs”。听起来 SQL 逻辑太宽泛了。
听起来您希望搜索将字符串视为全名（名字+姓氏），而不是要匹配的多个名称的列表。但是，如果提供了多个单词，您希望快速缩小范围以匹配所有单词而不是任何单词的记录。
名字可能很复杂，名字或姓氏是多个单词。这带来了一些小困难。
期望的结果：
名字或姓氏为“Allen”
名字和姓氏都匹配
针对“Allen”和/或“Smith”
此查询将允许搜索字符串中的任何单词数，但仅显示以某种方式匹配每个单词的记录（允许以任何顺序搜索名称，例如“First Last”或“Last First”）。这可能就是您想要的。
Chris,
I think the heart of your problem is that your logic is currently saying "Show me all records where FirstName or LastName matches one of the words in the search string". You are allowing someone to search "Matthew Mark Luke John" as a string to pull up people with ANY of those names, rather than treating the search word as a single name only.
This causes the fact that record 'Steve Jobs' comes up for search='Steve Smith' due to the match on firstName only. Sounds like the SQL logic is too broad.
Sounds like you want a search to instead treat the string as a full name (first name + lastname) instead of a list of multiple names to match against. However, if more than one word is provided, you want to narrow down quickly on records that match ALL words not ANY.
Names can be complicated things, with first or last name being mutiple words. This presents some minor difficulties.
Desired outcome:
with first or last name of 'Allen'
with BOTH first and last name matches
against 'Allen' and/or 'Smith'
This query will allow any # of words in search string but only show records that match each word in some fashion (allows searching names in any order, eg. First Last or Last First). This is probably what you want.
您用百分比包裹 LIKE 子句的两侧告诉我您确实想要一些模糊匹配。我会跳过 SQL WHERE 语句并使用全文解决方案。选项包括：
这两个选项都将使您的生活变得更加轻松，并避免您需要在 SQL WHERE 子句中引入复杂的 AND/OR LIKE 逻辑来实现某些类似的效果。
That you're wrapping both sides of your LIKE clauses with percents tells me you really want some fuzzy-ish matching. I would skip with SQL WHERE statements and use a full text solution instead. Options include:
Either of these options will make your life much easier and avoid the complex AND/OR LIKE logic you'd need to introduce in a SQL WHERE clause to achieve some similar effect.
做出几个假设，比如您将始终输入“名字”和“姓氏”来查找，并且您想要在存根上进行模糊搜索，例如 smith ... 返回 smith、smithers 等。这可能会成功。如果你确实想匹配铁匠、铁匠等，你可以放回前导%。
如果只搜索一个单词，你可以运行替代的“OR”SQL
Making a couple of assumptions, like that you will always enter a "firstname" and "lastname", to look for and that you want to fuzzy search on the stub e.g. smith ... returns smith, smithers etc. this might do it. You could put back in your leading % if you really want to match blacksmith, ironsmith, etc.
you could run an alternative "OR" SQL if only one word is being searched for