我一个电子商务公司工作，我们最近推出了一个新网站。我负责创建的搜索算法。

I work for an e-commerce company and we recently launched a new website. I am in charge of creating the search algorithm.

我们使用的是SQL Server数据库。我们使用标签，以联想的产品具有一定的产品关键字。我不希望使用任何我们的实际信息，所以我会代替实际的数据与其他的名字是什么。

We are using a SQL Server database. We are using 'tags' to associate products with certain product keywords. I don't want to use any of our actual information so I'll replace what the data actually is with other names.

有一个产品表，标签表和 ProductTags 表关联一个产品，一个标签。 A 标签可以有很多的产品，反之亦然。

There is a Product table, a Tags table, and a ProductTags table that associates a product with a tag. A Tag can have many products and vice versa.

标签表有：

int TagId
varchar TagName

产品表有：

Name
ProductId
Price

ProductTags 有：

TagId
ProductId
TagName
ProductName

现在我分裂在查询的空间搜索查询，并找到所有的'标签'适用于每一个字符串。

Right now I am splitting a search query on the spaces in the query and finding all of the 'Tags' applicable for each string.

这是例子查询看起来像：红色的鱼M60。

An example query would look like: 'Red fish M60'.

予得到的标签的从分裂在查询的空间后的查询中的每个串的列表。 下面是所有的标签的。

I get a list of Tags from each string in the query after splitting on the spaces in the query. The below are all 'Tag's.

串红的回报：

[Red]

鱼将返回：

[Fish_Male]
[Fish_Female]
[Fish_North_America]

和M60的回报：

[M60_connection]
[M60_secure]

和排名靠前的是搜索可能是一些产品的名称，如

and the top hits for that search may be some products with names like

'Red Male Fish Secure'
'Red Female Fish Secure'
'Red Male fish North america connection'

再低于就会少一些相关的产品，如

and then below those would be less relevant products like

'red male fish farmed'
'black female fish secure'

，然后跌破这一位置将不那么相关的结果，只有匹配一个标签如

and then below that would be less relevant results that only match one tag like

'red crab'

或

'black male fish'

我希望帮助。

I hope that helps.

所以我的问题是，什么是最好的方式（最快，最有效的？）我去通过这些标签和顶部返回最相关的产品，而不会错过任何产品上。

So my question is, what is the best way (fastest, most efficient?) for me to go through these tags and return the most relevant products at the top, while not missing any products as well.

是否可行，我把这些标签每一个可能的排列，并返回产品的每个排列？ （好像很多），以及返回产品的每个人的标签，就像在年底。

Is it feasible for me to take every possible permutation of these Tags and return the products for each permutation? (seems like a lot) As well as returning products for each individual Tag, like at the end.

例如，匹配三个标签的产品将在搜索结果的顶部，而产品仅匹配的那个'标签'将是在底部。用户将能够搜索标签的任何n个，因为有相当多的可用的。我放下只是一个简单的例子。让我知道，如果事情没有意义。

For example, products that match three 'Tags' would be at the top of the search results, while products only matching one 'Tag' would be at the bottom. The user would be able to search any n number of Tags, as there are quite a few available. What I put down was just a quick example. Let me know if something doesn't make sense.

感谢

推荐答案

这是一个开始。但我不知道，如果只是对标签命中数的匹配是足以满足您的排序。

This is a start. But I don't know if matching only on the count of tag hits is sufficient for your sorting.

select p.ProductId, count(*) as Relevance
from Product as p inner join ProductTags as pt on pt.ProductId = p.ProductId
where pt.TagId in (
    select TagId from Tags where TagName in (...)
)
group by p.ProductId
order by Relevance desc

顺便说一句，把产品名称和标记名的ProductTags表不归。

BTW, putting ProductName and TagName in the ProductTags table is not normalized.