如何在没有键的情况下连接表？

Question

是否可以在没有键情况下连接表，

table 1
------------------------------
no           code      area
------------------------------
0122133666   MOB       012221
92444444     LOC       9244
0343443434   DDD       03434
0092635252   INF       92
------------------------------

table 2
------------------------------
code         area      name
------------------------------
MOB          012       mobile-1
MOB          0129      mobile-2
LOC          9244      local-1
LOC          9243      local-2
INF          92        country-1
DDD          034       local-2
DDD          0344      local-3
------------------------------

我想要这样的结果

------------------------------------
no           code      area      name
------------------------------------
0122133666   MOB       012221   mobile-1
92444444     LOC       9244     local-1
0343443434   DDD       03434    local-2
0092635252   INF       92       country-1
--------------------------------------

看起来这是不可能的，有谁能帮我吗？

Answer 1

这将为您提供所需的结果：

SELECT  T1.no,
        T1.code,
        T1.area,
        T2.name
FROM Table1 T1
LEFT JOIN Table2 T2
  ON T1.code = T2.code
  AND T1.area LIKE CONCAT(T2.area,'%')

演示了这一点。

结果是：

╔═══════════╦══════╦════════╦═══════════╗
║    NO     ║ CODE ║  AREA  ║   NAME    ║
╠═══════════╬══════╬════════╬═══════════╣
║ 122133666 ║ MOB  ║ 012221 ║ mobile-1  ║
║  92444444 ║ LOC  ║   9244 ║ local-1   ║
║ 343443434 ║ DDD  ║  03434 ║ local-2   ║
║  92635252 ║ INF  ║     92 ║ country-1 ║
╚═══════════╩══════╩════════╩═══════════╝