SQL 文本匹配查询调优

发布于 2024-12-12 05:05:06 字数 673 浏览 0 评论 0原文

我正在尝试进行一些自由文本搜索匹配，想知道是否可以改进此查询（使用 MSSQL 2008）：

@FreeText 是一个表，其中每一行都是一个搜索词

DECLARE @WordCount = (SELECT COUNT(*) from @FreeText)

SELECT p.ID
FROM Product p

OUTER APPLY
(
    SELECT COUNT(ID) as MatchCount
    FROM Product pm
    INNER JOIN @FreeText ft
    ON pm.txt like '%'+ft.text+'%'
    WHERE pm.ID = p.ID
    AND (SELECT TOP 1 [text] FROM @FreeText) IS NOT NULL
)MC

WHERE MatchCount = @WordCount

所以我想知道是否有任何方法避免外部应用中的“FROM Product pm”？

我不能总是 INNER JOIN @FreeText 因为有时我们不使用自由文本搜索。

任何想法或提示将不胜感激，如果我可以澄清任何事情，也请告诉我。提前致谢。

PS 我确实知道 MS SQL 有 FREETEXT() 搜索，但不幸的是我目前无法使用它。

原文

I'm trying to do some free text search matching, and wondering if I can improve this query (using MSSQL 2008):

@FreeText is a table, where each row is a search word

DECLARE @WordCount = (SELECT COUNT(*) from @FreeText)

SELECT p.ID
FROM Product p

OUTER APPLY
(
    SELECT COUNT(ID) as MatchCount
    FROM Product pm
    INNER JOIN @FreeText ft
    ON pm.txt like '%'+ft.text+'%'
    WHERE pm.ID = p.ID
    AND (SELECT TOP 1 [text] FROM @FreeText) IS NOT NULL
)MC

WHERE MatchCount = @WordCount

So I'm wondering if there is any way to avoid the "FROM Product pm" in the outer apply?

I cannot always INNER JOIN @FreeText because sometimes we don't use free text searching.

Any thoughts or tips would be greatly appreciated, also let me know if I can clarify anything. Thanks in advance.

P.S. I do know that MS SQL has a FREETEXT() search, but I unfortunately cannot use that at the moment.

分享到QQ

分享到微博

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

发布评论

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

诗酒趁年少 2024-12-19 05:05:06

这是一个不带 OUTER APPLY 的查询，它在没有搜索条件时返回所有结果。

DECLARE @FreeText TABLE
(
  [text] varchar(200)
)
INSERT INTO @FreeText SELECT 'a'
INSERT INTO @FreeText SELECT 'c'

-- what, null?  No.
DELETE FROM @FreeText WHERE [text] is null

DECLARE @WordCount int
SET @WordCount = (SELECT Count(*) FROM @FreeText)

SELECT p.ID
FROM Product p
LEFT JOIN @FreeText ft
ON p.txt like '%' + ft.text + '%'
WHERE ft.text is not null OR @WordCount = 0
GROUP BY p.ID
HAVING COUNT(*) = @WordCount OR @WordCount = 0

注意：当没有任何自由文本条件时，我倾向于不使用“自由文本”查询 - 而是使用另一个查询（更简单）。如果您选择走这条路线 - 返回到 INNER JOIN 并删除 OR @WordCount = 0 x2。

Here's a query without OUTER APPLY, that returns all results when there are no search critera.

DECLARE @FreeText TABLE
(
  [text] varchar(200)
)
INSERT INTO @FreeText SELECT 'a'
INSERT INTO @FreeText SELECT 'c'

-- what, null?  No.
DELETE FROM @FreeText WHERE [text] is null

DECLARE @WordCount int
SET @WordCount = (SELECT Count(*) FROM @FreeText)

SELECT p.ID
FROM Product p
LEFT JOIN @FreeText ft
ON p.txt like '%' + ft.text + '%'
WHERE ft.text is not null OR @WordCount = 0
GROUP BY p.ID
HAVING COUNT(*) = @WordCount OR @WordCount = 0

Note: it would be my preference to not use the "freetext" query when there is not any freetext criteria - instead use another query (simpler). If you choose to go that route - go back to an INNER JOIN and drop the OR @WordCount = 0 x2.

回复收藏 0 原文

~没有更多了~