从由不同列数连接的两个表中进行选择

时间:2022-10-30 00:24:21

I have two tables. In first, I have a column which holds an alphanumeric with this format: X12345678A (1 letter, 8 numbers and 1 letter).

我有两张桌子。首先,我有一个包含这种格式的字母数字的列:X12345678A(1个字母,8个数字和1个字母)。

In second table, I have the same field, but divided into three columns, the first containing the leading letter, the second containing the number, and the third containing the trailing letter.

在第二个表中,我有相同的字段,但分为三列,第一列包含前导字母,第二列包含数字,第三列包含尾随字母。

My question: if I can't modify the table structure, how can I join both tables efficiently, ie, using an index?

我的问题:如果我不能修改表结构,我如何有效地加入两个表,即使用索引?

Thank you in advance!

先谢谢你!

1 个解决方案

#1


1  

With string concatenation:

使用字符串连接:

select . . .
from t1 join
     t2
     on t1.col = t2.col1 || t2.col2 || t2.col3;

For an efficient join, you can try an index on t1(col) or an index on the expression t2(col1 || col2 || col3).

对于有效的连接,您可以尝试t1(col)上的索引或表达式t2上的索引(col1 || col2 || col3)。

#1


1  

With string concatenation:

使用字符串连接:

select . . .
from t1 join
     t2
     on t1.col = t2.col1 || t2.col2 || t2.col3;

For an efficient join, you can try an index on t1(col) or an index on the expression t2(col1 || col2 || col3).

对于有效的连接,您可以尝试t1(col)上的索引或表达式t2上的索引(col1 || col2 || col3)。