根据子字符串连接2个SQL表 [英] Joining 2 sql tables based on substring
问题描述
我正在尝试联接只有两个键为table1.Account
和table2.key
I am trying to join tables where the only 2 keys which match are table1.Account
and table2.key
但是问题是安装程序. table1.Account
字段有10位数字,只有中间的4或5位数字与table2.key
But the problem is the setup. The table1.Account
field has 10 digits and only the middle 4 or 5 digits match with the table2.key
eg : 1234xxxx10 - > table1.Account matches with xxxx -> table2.key
or 123xxxxx10 - > table1.Account matches with xxxxx -> table2.key
我已经编写了这段INNER JOIN代码,但是查询一直在运行,并且没有给我任何回报.
I've written this piece of INNER JOIN code but the query keeps on running and is not giving me back anything.
SELECT DISTINCT column1, column2
from table1 INNER JOIN table2 ON table1.Account like '%'+table2.key+'%'
order by column1
推荐答案
SQL具有子字符串功能:
SQL has a substring function:
https://msdn.microsoft.com/en-us/library/ms187748.aspx
SELECT DISTINCT column1, column2
from table1
INNER JOIN table2
ON table1.Account = substring(table2.key, 3, 4)
OR table1.Account = substring(table2.key, 4, 4)
order by column1
我不知道您的on子句中是否需要'or',但是基于您的问题,这些字段似乎可能有两种匹配的方式.无论如何,您都可以根据需要修改on子句,但是此示例将帮助您使用语法,这似乎是您的障碍.
I don't know if you need the 'or' in your on clause, but based on your question it seems like there may be two ways for those fields to match up. Regardless, you can modify the on clause, as needed, but this sample should help you with the syntax, which appears to be your obstacle.
这篇关于根据子字符串连接2个SQL表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!