在子字符串数值上加入表格。

huangapple go评论64阅读模式
英文:

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".

huangapple
  • 本文由 发表于 2023年2月6日 11:03:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356990.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定