主页 / dba / 问题 / 330577
Accepted
AA.SC
Asked: 2023-08-24 17:23:52 +0800 CST

在多列上进行文本搜索而不启用全文搜索

  • 772

我有一个场景，我们需要在文本字段中启用多关键字搜索。此搜索应涉及在多个表（当前为 2 个表）中的多个列（大约 4-6 列）中扫描关键字。

问题，我可以使用简单的“AND”“OR”运算符构建查询

  1. 尝试 1 中的查询：返回超出所需的输出

  2. 尝试 2 中的查询：在输出中返回零记录

  3. 所需的输出：是尝试 1 输出中唯一的第一行

     -- truncate table #Customer

 -- truncate table #FaxDocs

    -- 创建表

     CREATE tABLE #FaxDocs ( [FaxDocID] INT , CustID INT,  RemoteCSID VARCHAR(20),  CSFileName VARCHAR(200))

 INSERT INTO #FaxDocs

 SELECT 1,1,'123ag','257831.PDF'

 UNION ALL 

 SELECT 2,1,'123yg','257837.PDF'

 UNION ALL 

 SELECT 3,2,'123g','257838.PDF'

 UNION ALL 

 SELECT 4,3,'123lg','257839.PDF'

 UNION ALL 

 SELECT 5,4,'123CK','257840.PDF'



 CREATE TABLE #Customer ( CustID INT , CFNAME VARCHAR(20),  CLNAME VARCHAR(200))

 insert into #Customer

 SELECT 1,'ABCNJSGHK', 'CDE'

 UNION ALL 

 SELECT 2,'CNJSGHK', 'CDE'

 UNION ALL 

 SELECT 3,'FGH', 'IJK'

 UNION ALL 

 SELECT 4,'LMN', 'OPQ'

    --- 搜索栏关键字

     DECLARE @Search VARCHAR(MAX) =  'NJSGHK 257831.PDF'     



     --CREATE TABLE #WordsToLookUp (Item VARCHAR(257), Location INT, Sequence INT PRIMARY KEY)



         INSERT INTO #WordsToLookUp(Item,[Location],[Sequence])

         SELECT 'NJSGHK',0,1

         UNION ALL 

         SELECT '257831.PDF',8,2



     ----------------------------------------------------------



          SELECT * FROM #WordsToLookUp

         --truncate table  #WordsToLookUp



 --- Attempt 1

          SELECT  [FaxDocID], Customer.CustID ,ISNULL(Customer.CFNAME,'') AS [FName], ISNULL(Customer.CLNAME,'') AS [LName] ,

             Faxes.CSFileName

             FROM #FaxDocs Faxes

             LEFT JOIN #Customer Customer ON Faxes.CustID = Customer.CustID 

             left join #WordsToLookUp B on Customer.CFNAME  like '%'+ b.item+'%'

             left join #WordsToLookUp c on Customer.CLNAME  like '%'+ c.item+'%'

             left join #WordsToLookUp d on Faxes.RemoteCSID  like '%'+ d.item+'%'

             left join #WordsToLookUp e on Faxes.CSFileName  like '%'+ e.item+'%'

             WHERE Faxes.[FaxDocID] in (1,2,3,4)



 -- Attempt 2

             SELECT  [FaxDocID], Customer.CustID ,ISNULL(Customer.CFNAME,'') AS [FName], ISNULL(Customer.CLNAME,'') AS [LName] ,

             Faxes.CSFileName

             FROM #FaxDocs Faxes

             LEFT JOIN #Customer Customer ON Faxes.CustID = Customer.CustID 

             WHERE Faxes.[FaxDocID] in (1,2,3,4)

             and exists (select 1 from #WordsToLookUp b where Customer.CFNAME  like '%'+ b.item+'%')

             and exists (select 1 from #WordsToLookUp c where Customer.CLNAME  like '%'+ c.item+'%')

             and exists (select 1 from #WordsToLookUp d where Faxes.RemoteCSID  like '%'+ d.item+'%')

             and exists (select 1 from #WordsToLookUp e where Faxes.CSFileName  like '%'+ e.item+'%')
sql-server

1 个回答

  1. Best Answer

    这将产生您想要的结果：

    WITH Searches
AS
(
    SELECT F.FaxDocID, F.CustID
        ,COALESCE(C.CFNAME,'') AS FName
        ,COALESCE(C.CLNAME,'') AS LName
        ,F.RemoteCSID, F.CSFileName
        ,COUNT(1) OVER (PARTITION BY F.FaxDocID, F.CustID) AS Matches
    FROM #FaxDocs F
        LEFT JOIN #Customer C
            ON F.CustID = C.CustID
        CROSS APPLY
        (
            VALUES (C.CFNAME), (C.CLNAME), (F.RemoteCSID), (F.CSFileName)
        ) X (String2Search)
        LEFT JOIN #WordsToLookUp L
            ON X.String2Search LIKE '%' + L.item + '%'
    WHERE F.FaxDocID < 5
        AND L.Item IS NOT NULL
)
SELECT DISTINCT FaxDocID, CustID, FName, LName, RemoteCSID, CSFileName
FROM Searches
WHERE Matches = (SELECT COUNT(1) FROM #WordsToLookUp);

    你可能想读一下卦象：

    https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search

    • 1

相关问题