SQL Server 使用通配符加入并在第一场比赛时停止 [英] SQL Server Join with Wildcard and stop on first match

问题描述

    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 使用通配符加入并在第一场比赛时停止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！