TSQL订单结果，其中列包含列表中的所有值

I have a Movies table with a Title column.

我有一个带有标题栏的电影表。

"The a"
“一个”
"the b"
“b”
"the c"
“c”
"the something"
“那东西”

When I query that column with the input "the something" - I am returning all movies with a title containing EITHER "the" OR "something". This is what I want.

当我用输入“the something”查询该列时 - 我将返回所有带有标题的电影，其中包含“或”某事“。这就是我要的。

My current issue is that I am unable to order my results first by titles that contain all of the words supplied in the input. Since the word "the" is so vague, many titles match it, and the titles with "something" get buried in the results list.

我目前的问题是，我无法首先通过包含输入中提供的所有单词的标题来排序我的结果。由于“the”这个词是如此模糊，许多标题都与之匹配，而带有“某事”的标题则被埋没在结果列表中。

In this example when I query for "the something" - "the something" should be ordered at the top. But since the query matches all columns containing both "the" and "something" I don't know how to achieve this.

在这个例子中，当我查询“东西”时 - “东西”应该在顶部排序。但由于查询匹配包含“the”和“something”的所有列，我不知道如何实现这一点。

I need Movies.Title to be ordered by columns that contain all values from SplitStringTable where possible. If that is possible.

我需要Movies.Title按可能包含SplitStringTable中所有值的列进行排序。如果可以的话。

How I achieve my query is by performing an JOIN on the table with the list of values supplied in the input:

我如何实现我的查询是通过在表上使用输入中提供的值列表执行JOIN：

SELECT * FROM Movies m 
JOIN (SELECT * FROM dbo.splitstring(@input, ' ')) SplitStringTable 
ON m.Title LIKE '%' + SplitStringTable .[Value] + '%'

Any thoughts on how to achieve this would be greatly appreciated.

任何关于如何实现这一点的想法将不胜感激。

EDIT: Pardon me everyone, I explained my scenario all wrong. I am returning any record where Title contains ANY of the values from the SplitStringTable. I apologize for the confusion.

编辑：原谅我所有人，我解释我的情况都错了。我将返回任何记录，其中Title包含SplitStringTable中的任何值。我为这种困惑道歉。

1 个解决方案

#1

Your query will return a row for each word matched; so you could group by the fields in the Movies record and then ORDER BY count(*) DESC. Since I don't know all the fields I can show you only approximately what I mean:

您的查询将为匹配的每个单词返回一行;所以你可以按照电影记录中的字段进行分组，然后按ORDER BY计数（*）DESC。由于我不知道所有领域，我只能向您展示我的意思：

SELECT m.TITLE
  FROM      Movies m 
       JOIN (SELECT * FROM dbo.splitstring(@input, ' ')) s 
         ON m.Title LIKE '%' + s.[Value] + '%'
 GROUP BY m.TITLE
 ORDER BY count(*) DESC

But unless TITLE is unique this won't work right. To make sure it orders the rows correctly, and to be able to see all the other fields, you need to include each field of the Movies table in the GROUP BY expression.

但除非TITLE是唯一的，否则这将无法正常工作。要确保它正确排序行，并且能够查看所有其他字段，您需要在GROUP BY表达式中包含Movies表的每个字段。

As an aside, there are probably better tools than SQL for this type of search, though perhaps you'll get results you find sufficient. But one refinement I would suggest:

顺便说一句，对于这种类型的搜索，可能有比SQL更好的工具，不过你可能会得到足够的结果。但我会建议一个改进：

ON ' ' || m.Title || ' ' LIKE '% ' + s.[Value] + ' %'

may help avoid matching "the" against "theory", "another", etc. If you can always rely on whole-word matches, that is...

可能有助于避免将“the”与“理论”，“另一个”等相匹配。如果你总能依靠全字匹配，那就是......

#1

SELECT m.TITLE
  FROM      Movies m 
       JOIN (SELECT * FROM dbo.splitstring(@input, ' ')) s 
         ON m.Title LIKE '%' + s.[Value] + '%'
 GROUP BY m.TITLE
 ORDER BY count(*) DESC