c# - 将所有列与所有搜索词组匹配

Question

我想让用户在表格的所有列中搜索文本框中定义的一组短语(用空格分隔术语)。所以首先想到的是在 SQL 中找到一种连接所有列的方法，并在此结果中仅使用 LIKE 运算符(对于每个短语)。我想到的另一个解决方案是编写一个算法，该算法采用所有搜索到的短语，并将它们与所有列相匹配。所以我得到了以下结果:

String [] columns = {"col1", "col2", "col3", "col4"};
String [] phrases = textBox.Text.Split(' ');

然后我采用了所有可能的列和短语组合，并将其放入 sql 的 where-clause-format 中，结果是

"(col1 LIKE '%prase1%' AND col1 LIKE '%phrase2%') OR
(col1 LIKE '%phrase1%' AND col2 LIKE '%phrase2%') OR
(col1 LIKE '%phrase2%' AND col2 LIKE '%phrase1%') OR
(col2 LIKE '%phrase1%' AND col3 LIKE '%phrase2%')"

以上只是输出的示例片段，在此算法中创建的条件数量由

conditions=columns^(phrases+1)

所以我观察到有 2 个搜索短语仍然可以提供良好的性能，但超过这个数量肯定会大大降低性能。

在所有列中搜索相同数据时，最佳做法是什么？

Answer 1

埃德温，

我不知道您在使用 ORACLE。我的解决方案是使用 SQL Server。希望您能了解解决方案的要点并将其转化为 PL/SQL。

希望这对你有用。

我正在手动填充#search 临时表。你需要以某种方式做到这一点。或者寻找一些将采用分隔字符串并返回表的拆分函数。

IF OBJECT_ID('tempdb..#keywords') IS NOT NULL
    DROP TABLE #keywords;

IF OBJECT_ID('tempdb..#search') IS NOT NULL
    DROP TABLE #search;

DECLARE @search_count INT

-- Populate # search with all my search strings
SELECT *
INTO #search
FROM (
    SELECT '%ST%' AS Search

    UNION ALL

    SELECT '%CL%'
    ) T1

SELECT @search_count = COUNT(*)
FROM #search;

PRINT @search_count

-- Populate my #keywords table with all column values from my table with table id and values
-- I just did a select id, value union with all fields
SELECT *
INTO #keywords
FROM (
    SELECT client_id AS id
        ,First_name AS keyword
    FROM [CLIENT]

    UNION

    SELECT client_id
        ,last_name
    FROM [CLIENT]
    ) AS T1

-- see what is in there 
SELECT *
FROM #search

SELECT *
FROM #keywords

-- I am doing a count(distinct #search.Search). This will get me a count, 
--so if I put in 3 search values my count should equal 3 and that tells me all search strings have been found
SELECT #keywords.id
    ,COUNT(DISTINCT #search.Search)
FROM #keywords
INNER JOIN #search ON #keywords.keyword LIKE #search.Search
GROUP BY #keywords.id
HAVING COUNT(DISTINCT #search.Search) = @search_count

SELECT *
FROM [CLIENT]
WHERE [CLIENT].client_id IN (
        SELECT #keywords.id
        FROM #keywords
        INNER JOIN #search ON #keywords.keyword LIKE #search.Search
        GROUP BY #keywords.id
        HAVING COUNT(DISTINCT #search.Search) = @search_count
        )