英文:
Join table on substring-ed values
问题
所以基本上,我有两个表,X和Y。我想要使用子字符串的值将它们连接在一起,但我不确定如何做到这一点。
X的示例代码:
select distinct
trim(ws.WS_name) as [WS Name],
substring(trim(e.equip_id),1,8) as [Equid ID],
into #eqm_hist
from eqmhist
X的示例表:
WS name | Equid ID |
---|---|
WS 21 | KTRGAF7L |
WS 21 | KTRGAF7L |
Y的示例代码:
select distinct
[Batch ID],
[Qty],
rtrim(flh.tracking_interface_id) as [Tool],
[WS Name],
into #main
from flh
Y的示例表:
Batch ID | Qty | Tool |
---|---|---|
12345 | 100 | KTRGAF7L00 |
23456 | 100 | KTRGAF7L00 |
请注意,[Tool]和[Equid ID]是相同的。
如何使用子字符串的值将它们连接在一起以获得如下表所示的表?我是否还需要删除表Y中的[Tool]的最后两位数字来实现这一点?
Batch ID | Qty | Tool | WS Name |
---|---|---|---|
12345 | 100 | KTRGAF7L00 | WS 21 |
23456 | 100 | KTRGAF7L00 | WS 21 |
英文:
So basically, I have two table, X and Y. I want to join them with the substring-ed value but I'm unsure of how I can do so.
Sample code of X:
select distinct
trim(ws.WS_name) as [WS Name],
substring(trim(e.equip_id),1,8) as [Equid ID],
into #eqm_hist
from eqmhist
Sample table of X:
WS name | Equid ID |
---|---|
WS 21 | KTRGAF7L |
WS 21 | KTRGAF7L |
Sample code of Y:
select distinct
[Batch ID],
[Qty],
rtrim(flh.tracking_interface_id) as [Tool],
[WS Name],
into #main
from flh
Sample table of Y:
Batch ID | Qty | Tool |
---|---|---|
12345 | 100 | KTRGAF7L00 |
23456 | 100 | KTRGAF7L00 |
Do note that [Tool] and [Equid ID] is the same
How can I join them together using the substring-ed values to achieve the table like below?
Do I also have to remove the last two digit for [Tool] in table Y to do so?
Batch ID | Qty | Tool | WS Name |
---|---|---|---|
12345 | 100 | KTRGAF7L00 | WS 21 |
23456 | 100 | KTRGAF7L00 | WS 21 |
答案1
得分: 1
根据这个问题的第一个回答,看起来SQL Server将会优化一个LIKE 'prefix%'
条件以使用索引。
所以,如果在Y(Tool)上有一个索引,你应该可以高效地运行以下查询:
SELECT *
FROM X
JOIN Y ON Y.Tool LIKE X.[Equid ID] + '%'
跟进说明:如果[Equid ID]的值可能有不同的长度,但是工具的值始终会有两个额外的字符,你应该将连接条件更改为ON Y.Tool LIKE X.[Equid ID] + '__'
。下划线是单字符通配符。这将处理这样的情况,其中你有[Equid ID]值"Thing"和"ThingA",以及Tool值"Thing01","Thing02","ThingA01"和"ThingA02"。
英文:
Per the first answer to this question, it appears that SQL Server will optimize a LIKE 'prefix%'
condition to use an index.
So if you have an index on Y(Tool), you should be able to efficiently run the query:
SELECT *
FROM X
JOIN Y ON Y.Tool LIKE X.[Equid ID] + '%'
Follow-up note: If [Equid ID] values may have variable length, but the tool values will always have exactly two extra characters, you should change join condition to ON Y.Tool LIKE X.[Equid ID] + '__'
. Underscores are single-character wildcards. This will handle vases where you have [Equid ID] values "Thing" and "ThingA" with Tool values "Thing01", "Thing02", "ThingA01", and "ThingA02".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论