SQL连接与通配符 [英] SQL Join with wildcards

问题描述

说我有两个表:table1如下

Say I have two tables: table1 as follows

Name     |Surname| col1
------------------------
 Bob     |Smith  | BS1
Mary Jane|Jones  | MJ1

和表2如下:

Name     |Surname       | col2
------------------------------
 Bob     |Keller Smith  | BS2
Mary     |Jones         | MJ2

我想要将这些结合在一起以获得:

What I would like is to JOIN these together to obtain:

Name     |Surname       | col1| col2
-------------------------------------
 Bob     |Keller Smith  | BS1 | BS2
Mary     |Jones         | MJ1 | MJ2

我尝试过:

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
ON '%'+tablea.Name+'%' LIKE '%'+tableb.Name+'%' AND '%'+tablea.Surame+'%' LIKE '%'+tableb.Surame+'%'

但是这些似乎都融入了一切.

But these seemed to join everything.

如何正确地将带有通配符的列连接在一起?

How can I join together columns with wildcards correctly?

推荐答案

尝试一下:

SELECT tableb.Name, tableb.Surname, tablea.col1, tableb.col2
FROM table1 as tablea
LEFT JOIN table2 as tableb
    ON (tablea.Name LIKE '%' + tableb.Name + '%' OR tableb.Name LIKE '%' + tablea.Name + '%')
    AND (tablea.Surname LIKE '%' + tableb.Surname + '%' OR tableb.Surname LIKE '%' + tablea.Surname + '%')

这篇关于SQL连接与通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！