如何让sql搜索查询更强大？

I wrote this sql query to search in a table:

我写了这个SQL查询来搜索表：

SELECT * FROM TableName WHERE Name LIKE '%spa%'

The table contain these row for example:

该表包含这些行，例如：

Space Company.
航天公司。
Spa resort.
温泉度假村。
Spa hotel.
温泉酒店。
Spare Parts.
备件。
WithoutTheKeyword.
WithoutTheKeyword。

I want to know how to edit this query so it return the results sorted like this:

我想知道如何编辑此查询，以便返回如下排序结果：

2 Spa resort

2个温泉度假村

3 Spa hotel

3间Spa酒店

1 Space Company

1空间公司

4 Spare Parts

4备件

Means the items which contain the exact word first then the like ones.

表示首先包含确切单词然后包含相同单词的项目。

6 个解决方案

#1

something like

就像是

Select * from TableName where Name Like 'Spa%'
ORDER BY case when soundex(name) = soundex('Spa') then '1' else soundex(name) end

should work ok.

应该工作正常。

actually this will work better

实际上这会更好

Select * from TableName where Name Like 'Spa%'
ORDER BY DIFFERENCE(name, 'Spa') desc;

FWIW I did some quick tests and if 'Name' is in a NONCLUSTERED INDEX SQL will use the index and doesn't do a table scan. Also, LIKE seems to use less resources than charindex (which returns less desirable results). Tested on sql 2000.

FWIW我做了一些快速测试，如果'Name'在NONCLUSTERED INDEX中SQL将使用索引而不进行表扫描。此外，LIKE似乎比charindex使用更少的资源（返回不太理想的结果）。在sql 2000上测试过。

#2

You realize, I presume, that your schema just about eliminates any usefulness of indexes for these kinds of queries?

我认为，你认为你的架构只是为这些类型的查询消除了索引的任何用处？

A big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan.

一个很大的问题是你的“LIKE'％spa％'”。任何以通配符开头的“LIKE”键都是自动表扫描。

EDIT: I read your question to say that there is a single field, Name, with field values something like "1 Space Company", "2 Spa resort", etc. with a number followed by words. And you needed the wild card in front of your search key to get past the number part. (This is to clarify my first comment.) Am I guessing correctly or not?

#3

The following should do the necessary, but it's inefficient, doing two full table selects and it also relies on your exact match being delimited by spaces. I think FullText indexing would help, but that has overheads of its own.

以下应该做必要的，但它效率低，做两个全表选择，它还依赖于你用空格分隔的完全匹配。我认为FullText索引会有所帮助，但这有其自身的开销。

select distinct * from
(
Select * from TableName 
   where CHARINDEX('spa ', Name) > 0
   or CHARINDEX(' spa', Name) > 0
Union
Select * from TableName 
  where Name Like '%spa%'
)

#4

Going off the top example, at least in MSSQL2005 changing the CLUSTERED to NONCLUSTERED will make it do a table scan. CLUSTERED gives you an index seek. Looks like it matches the conditions of the question.

离开顶部示例，至少在MSSQL2005中将CLUSTERED更改为NONCLUSTERED将使其进行表扫描。 CLUSTERED为您提供索引搜索。看起来它符合问题的条件。

CREATE TABLE tblTest(ID INT, colname VARCHAR(20) )
CREATE CLUSTERED INDEX tstidx1_tblTest ON tblTest(colname);
INSERT tblTest SELECT 1,'Space Company'
INSERT tblTest SELECT 2,'Spa Resort'
INSERT tblTest SELECT 3,'Spa Hotel'
INSERT tblTest SELECT 4,'Spare Parts'
INSERT tblTest SELECT 5,'WithoutTheKeyword'

SELECT * FROM tblTest WHERE colname LIKE 'Spa%'
ORDER BY DIFFERENCE(colname,'Spa') DESC;

DROP TABLE tblTest

#5

You basically need to define (precisely) what your ranking function really is. What if you have a row that is "The Spa." or "spa.com"? Once you have that defined, you need to put that logic into your ORDER BY clause. For example:

您基本上需要（精确地）定义您的排名功能。如果您有一排“The Spa”怎么办？还是“spa.com”？一旦定义了，就需要将该逻辑放入ORDER BY子句中。例如：

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%spa%'
ORDER BY
    CASE
        WHEN name LIKE '% ' + @search_word + ' %' THEN 1   -- Notice the spaces
        ELSE 2
    END,
    name

Alternatively, you could write a ranking function and use that:

或者，您可以编写排名函数并使用：

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%' + @search_word + '%'
ORDER BY
    dbo.GetNameMatchRank(name, @search_word)

Performance on very large result sets may not be too great, so this approach depends on your expected search result sizes.

非常大的结果集上的性能可能不会太大，因此这种方法取决于您预期的搜索结果大小。

#6

-1

This should work:

这应该工作：

Select * from TableName where Name Like '%spa%'
ORDER BY Name

#1