问题描述
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.