如何使sql搜索查询更强大? [英] How to make a sql search query more powerful?

问题描述

我编写了此sql查询以在表中进行搜索:

I wrote this sql query to search in a table:

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

该表包含以下行，例如:

The table contain these row for example:

1. 太空公司.
2. 温泉度假村.
3. 温泉酒店.
4. 备件.
5. 没有关键字.

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

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

2个水疗胜地

2 Spa resort

3家温泉酒店

1个太空公司

4个备件

表示首先包含确切单词的项目，然后是类似的项目.

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

推荐答案

类似

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

应该可以.

实际上这会更好

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

首先，我做了一些快速测试，如果名称"在NONCLUSTERED INDEX中，则SQL将使用索引，并且不进行表扫描.同样，LIKE似乎比charindex使用更少的资源(charindex返回的期望结果更少).在sql 2000上进行了测试.

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.

这篇关于如何使sql搜索查询更强大?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！