問(wèn)題描述

    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

結(jié)果

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

在上面的代碼中，我在 join 中使用通配符.我面臨的問(wèn)題是結(jié)果BB01BC"行出現(xiàn)了兩次，因?yàn)樗瑫r(shí)包含BB"和BC"字符.有沒(méi)有辦法讓它只出現(xiàn)一次.因此，如果BB"與BB01BC"匹配，那么它不應(yīng)該在其中查找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 時(shí)，您幾乎總是需要 ORDER BY.您似乎對(duì)T2 中的哪 行匹配并不特別感興趣，您只想要其中之一.如果您有特定的優(yōu)先級(jí)，請(qǐng)?zhí)砑?ORDER BY 以進(jìn)行優(yōu)先級(jí)排序.

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.

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