在CONTAINS函数中使用JOIN语句 [英] Using JOIN statement with CONTAINS function

问题描述

在SQL Server数据库中，我有一个包含很多INNER JOINs语句的View.最后一个连接使用LIKE谓词，这就是为什么它工作太慢的原因.查询如下:

In SQL Server database I have a View with a lot of INNER JOINs statements. The last join uses LIKE predicate and that's why it's working too slowly. The query looks like :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON X.Name LIKE '%' + W.Name  + '%' AND
            LIKE '%' + W.Name2  + '%' AND
            LIKE '%' + W.Name3  + '%'

我想使用CONTAINS代替LIKE:

I want to use CONTAINS instead of LIKE as :

SELECT *
FROM A INNER JOIN
B ON A.ID = B.ID INNER JOIN
C ON C.ID1 = B.ID1 INNER JOIN
...........................
X ON CONTAINS(X.Name, W.Name) AND
     CONTAINS(X.Name, W.Name2) AND
     CONTAINS(X.Name, W.Name3)

我知道CONTAINS的运行速度比LIKE快，并且无法在JOIN语句中使用CONTAINS. 在这种情况下或建议中有什么解决方法吗? 预先感谢.

I know that CONTAINS is working faster than LIKE and also that can't use CONTAINS in JOIN statements. Is there any workaround in this case or suggestion? Thanks in advance.

推荐答案

不是CONTAINS不能在联接中使用.

It's not that CONTAINS can't be used in joins.

您只是不能将列用作CONTAINS的第二个参数-请参见 MSDN-包含(Transact-SQL)

You just can't use columns as a second parameter of CONTAINS - see MSDN - CONTAINS (Transact-SQL)

CONTAINS
( { column_name | ( column_list ) | * } 
  ,'<contains_search_condition>'     
[ , LANGUAGE language_term ]
) 

但是，可以将变量用作搜索条件，因此可以使用游标，然后获取所需的所有数据. 这是一些非常粗糙的示例:

However, you can use a variable as a search condition, so you can use a cursor and then get all data you need. Here is some very rough example:

declare @Name nvarchar(max)

declare @Temp_A table(Name nvarchar(max))
declare @Temp_B table(Name nvarchar(max))

--=============================================================================================
insert into @Temp_A (Name)
select 'Test'

insert into @Temp_B (Name)
select 'aaaTestaaa'

--=============================================================================================
-- Query 1 - LIKE
--=============================================================================================
select *
from @Temp_A as A
    inner join @Temp_B as B on B.Name like '%' + A.Name + '%'

--=============================================================================================
-- Query 2 - CONTAINS
--=============================================================================================
declare table_cursor cursor local fast_forward for
    select distinct Name from @Temp_A
open table_cursor
while 1 = 1
begin
    fetch table_cursor into @Name
    if @@fetch_status <> 0 break

    select * from @Temp_B where contains(Name, @Name)
end
close table_cursor
deallocate table_cursor

这篇关于在CONTAINS函数中使用JOIN语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！