如何根据关键词搜索进行排序？

发布于 2024-09-08 12:44:51 字数 925 浏览 2 评论 0原文

我的 sql 语句

SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))

如何列出像这样的顶部匹配结果

Green
Army Green
Hunter Green

而不是下面的结果

Army Green
Green
Hunter Green

我知道我们不能在我的情况下使用按 asc 或 desc 的顺序。

我计划在“绿色”之前添加一个空格并使用字典对象或插入数据库并按字母顺序对其进行排序。我认为这里有一种更好的技术可以完成这项工作，而不是使用我的缓慢技术专门处理关键字数据库中超过数百万条的记录。

顺便说一下，我们还在使用古老的SQL Server 2000。

这是与绿色相关的关键字。我喜欢根据输入的关键字获得最匹配的结果。结果应该显示与关键字最接近的匹配。

数据库中的数据

Army Green
Blue-Green
Bright Green
British Racing Green
Camouflage Green
Dark Green
Dark Pastel Green
Electric Green
Fern Green
Forest Green
Green
Green-Yellow
Hunter Green
India Green
Islamic Green
Jungle Green
Kelly Green
Lawn Green
Lime Green
Moss Green
MSU Green
Office Green
Pastel Green
Persian Green
Pine Green
Sea Green
Shamrock Green
Spring Green
Tea Green
Yellow-Green

原文

My sql statement

SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))

How do I list the top matched result like this

Green
Army Green
Hunter Green

instead of the below

Army Green
Green
Hunter Green

I know that we can't use the order by asc or desc with my case.

I plan to add a space before 'green' and use the dictionary object or insert into the database and sort it alpha order. I think there is a better technique out here that can do the job instead of using my slow technique specially dealing with more than multi-million records in the keyword database.

By the way, we are still using the ancient SQL Server 2000.

this is the keywords which is related to green. I like to get the top matched result based on the input keyword. The result should show the closest match to the keyword.

Data in the database

Army Green
Blue-Green
Bright Green
British Racing Green
Camouflage Green
Dark Green
Dark Pastel Green
Electric Green
Fern Green
Forest Green
Green
Green-Yellow
Hunter Green
India Green
Islamic Green
Jungle Green
Kelly Green
Lawn Green
Lime Green
Moss Green
MSU Green
Office Green
Pastel Green
Persian Green
Pine Green
Sea Green
Shamrock Green
Spring Green
Tea Green
Yellow-Green

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抱着落日 2024-09-15 12:44:51

也许...

SELECT *  
    FROM mytable 
    WHERE (CONTAINS(keyword,' "green" '))
    ORDER BY FIND_IN_SET('green', keyword);

Find_in_set MySQL 不过；你可能没有。它仅返回关键字列中单词“Green”的位置。

SQL Server 似乎有 CHARINDEX('green', keywords)（如果有帮助的话）。

Perhaps...

SELECT *  
    FROM mytable 
    WHERE (CONTAINS(keyword,' "green" '))
    ORDER BY FIND_IN_SET('green', keyword);

Find_in_set MySQL though; you may not have it. It just returns the position of the word 'Green' in the keyword column.

SQL Server appears to have CHARINDEX('green', keyword) if that's any help.

回复收藏 0 原文

甜嗑 2024-09-15 12:44:51

好吧，您可以特殊情况下完全匹配：

SELECT *  
  FROM mytable 
 WHERE keyword = 'green'
UNION ALL
SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))
   AND keyword <> 'green'

Well, you can special-case exact matches:

SELECT *  
  FROM mytable 
 WHERE keyword = 'green'
UNION ALL
SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))
   AND keyword <> 'green'

回复收藏 0 原文