問題描述

    IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
    IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

    CREATE TABLE #TABLE1
    (
        CODE_NAME_T1 NVARCHAR(20)
    )

    CREATE TABLE #TABLE2
    (
        CODE_NAME_T2 NVARCHAR(20)
    )

    INSERT INTO #TABLE1(CODE_NAME_T1)
    VALUES             ('BBX123')
                      ,('BC/230')
                      ,('1AC030')
                      ,('BB01BC')           

    INSERT INTO #TABLE2(CODE_NAME_T2)
    VALUES             ('BB')
                      ,('BC')

    SELECT T1.CODE_NAME_T1, T2.CODE_NAME_T2
    FROM #TABLE1 T1
    LEFT OUTER JOIN #TABLE2 T2
    ON T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%'

    IF OBJECT_ID('tempdb..#TABLE1') IS NOT NULL DROP TABLE #TABLE1
    IF OBJECT_ID('tempdb..#TABLE2') IS NOT NULL DROP TABLE #TABLE2

結果

CODE_NAME_T1   |    CODE_NAME_T2
---------------|-----------------
BBX123         |     BB
BC/230         |     BC
1AC030         |     NULL
BB01BC         |     BB
BB01BC         |     BC

在上面的代碼中，我在 join 中使用通配符.我面臨的問題是結果BB01BC"行出現了兩次，因為它同時包含BB"和BC"字符.有沒有辦法讓它只出現一次.因此，如果BB"與BB01BC"匹配，那么它不應該在其中查找BC"?基本上只做一次匹配/查找而不做更多的匹配/查找?

Hi, in above code I am using wildcard in join. The problem I am facing that in result "BB01BC" row is appearing twice as it contains both "BB" and "BC" characters. Is there way that it only appears once. So if "BB" is matched in "BB01BC" then it should not look for "BC" in it? Basically only doing one match/lookup and not doing more match/lookup?

推薦答案

這是一種使用 OUTER APPLY 的方法:

Here is one method using OUTER APPLY:

SELECT T1.CODE_NAME_T1, T2.CODE_NAME_T2
FROM #TABLE1 T1 OUTER APPLY
     (SELECT TOP 1 t2.*
      FROM #TABLE2 T2
      WHERE T1.CODE_NAME_T1 LIKE '%' + T2.CODE_NAME_T2 + '%'
     ) T2;

注意:在使用 TOP 時，您幾乎總是需要 ORDER BY.您似乎對T2 中的哪 行匹配并不特別感興趣，您只想要其中之一.如果您有特定的優先級，請添加 ORDER BY 以進行優先級排序.

Note: You almost always want an ORDER BY when using TOP. You don't seem particularly interested in which row from T2 matches, you just want one of them. If you have a particular priority, then add ORDER BY for prioritization.

這篇關于SQL Server 使用通配符加入并在第一場比賽時停止的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！