I'm trying to perform a CONTAINS query with multiple terms over multiple columns, like this:
我正在尝试在多个列上执行包含多个术语的CONTAINS查询,如下所示:
SELECT ID
FROM Table
WHERE CONTAINS((Data1,Data2,Data3), '"foo" & "bag" & "weee"')
However, this query does not behave like I want it to: I want it to return all records for which all terms appear at least once in at least one of the columns, like this:
但是,此查询的行为与我想要的不一样:我希望它返回所有记录,其中所有条目在至少一个列中至少出现一次,如下所示:
SELECT ID
FROM Table
WHERE CONTAINS((Data1,Data2,Data3), '"foo"')
AND CONTAINS((Data1,Data2,Data3), '"bag"')
AND CONTAINS((Data1,Data2,Data3), '"weee"')
While this query returns the correct results, it needs a separate AND-clause for every term. Is there a way to express the same query with a single where-clause like in the upper example? This would be convenient when including the query in a (fixed) function.
虽然此查询返回正确的结果,但每个术语都需要一个单独的AND子句。有没有办法用上面的例子中的单个where子句表达相同的查询?当在(固定)函数中包含查询时,这将是方便的。
1 个解决方案
#1
9
SQL Server
once behaved this way, but it was considered a bug and "corrected".
SQL Server曾经以这种方式表现,但它被认为是一个错误并“纠正”。
You need to create a FULLTEXT
index on a computed column:
您需要在计算列上创建FULLTEXT索引:
DROP TABLE t_ft
CREATE TABLE t_ft (id INT NOT NULL,
data1 NVARCHAR(MAX) NOT NULL, data2 NVARCHAR(MAX) NOT NULL, data3 NVARCHAR(MAX) NOT NULL,
totaldata AS data1 + ' ' + data2 + ' ' + data3,
CONSTRAINT pk_ft_id PRIMARY KEY (id))
CREATE FULLTEXT INDEX ON t_ft (totaldata LANGUAGE 1033) KEY INDEX pk_ft_id
INSERT
INTO t_ft
VALUES (1, 'foo bar', 'baz', 'text')
INSERT
INTO t_ft
VALUES (2, 'foo bar', 'bax', 'text')
SELECT *
FROM t_ft
WHERE CONTAINS (*, 'foo AND baz')
In MySQL
, on the contrary, the fulltext index searches and matches across all columns and this is a documented behavior.
相反,在MySQL中,全文索引在所有列中进行搜索和匹配,这是一种记录在案的行为。
#1
9
SQL Server
once behaved this way, but it was considered a bug and "corrected".
SQL Server曾经以这种方式表现,但它被认为是一个错误并“纠正”。
You need to create a FULLTEXT
index on a computed column:
您需要在计算列上创建FULLTEXT索引:
DROP TABLE t_ft
CREATE TABLE t_ft (id INT NOT NULL,
data1 NVARCHAR(MAX) NOT NULL, data2 NVARCHAR(MAX) NOT NULL, data3 NVARCHAR(MAX) NOT NULL,
totaldata AS data1 + ' ' + data2 + ' ' + data3,
CONSTRAINT pk_ft_id PRIMARY KEY (id))
CREATE FULLTEXT INDEX ON t_ft (totaldata LANGUAGE 1033) KEY INDEX pk_ft_id
INSERT
INTO t_ft
VALUES (1, 'foo bar', 'baz', 'text')
INSERT
INTO t_ft
VALUES (2, 'foo bar', 'bax', 'text')
SELECT *
FROM t_ft
WHERE CONTAINS (*, 'foo AND baz')
In MySQL
, on the contrary, the fulltext index searches and matches across all columns and this is a documented behavior.
相反,在MySQL中,全文索引在所有列中进行搜索和匹配,这是一种记录在案的行为。