SQL 如何根据字段的子字符串值连接两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28636051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to join two tables based on substring values of fields?
提问by userx
I am having problem with sql. I want to join two tables, employee and class instructor. Condition is that employee is having unid column like 'u0871457' where as class instructor is having EmplId as '00871457'.
我在使用 sql 时遇到问题。我想加入两个表，员工和班主任。条件是员工有像'u0871457'这样的unid列，而班级讲师的EmplId为'00871457'。
I just want to replace the first character of EmplId to 'u' to join to match the string coming from unid . How can I do that? I have tried this so far :
我只想将 EmplId 的第一个字符替换为 'u' 以加入以匹配来自 unid 的字符串。我怎样才能做到这一点？到目前为止我已经尝试过这个：
select e.name, i.name
from Employee e
inner join Instructor i on SUBSTR(e.id,1, LENGTH(e.id )) = SUBSTR(i.id,1, LENGTH(i.id ))
but this is resulting into a blank resultset.
但这会导致空白结果集。
Any help will be appreciated. Thanks for your time!
任何帮助将不胜感激。谢谢你的时间！
回答by David Faber
So many ways to do this. It would be a good idea to look at the explain plan for various ways before committing to a particular method. For example, if there is a function-based index on EMPLOYEEsuch as SUBSTR(id, 2, LENGTH(id) - 1)then you'll want to use that in your query:
有很多方法可以做到这一点。在采用特定方法之前，最好先查看各种方式的解释计划。例如，如果有一个基于函数的索引EMPLOYEE，例如SUBSTR(id, 2, LENGTH(id) - 1)，那么你将要使用在查询：
SELECT e.name, i.name
FROM employee e INNER JOIN instructor i
ON SUBSTR(e.id, 2, LENGTH(e.id) - 1) = SUBSTR(i.id, 2, LENGTH(i.id) - 1);
Another question is if the values in the idcolumn are always the same length in EMPLOYEEandINSTRUCTOR. What if they are of differing lengths? Maybe one has more padding than another. Also, will they always be digitsapart from a leading u? If so, then it might be worthwhile to try a safe TO_NUMBER()conversion:
另一个问题是id列中的值是否在EMPLOYEE和 中的长度始终相同INSTRUCTOR。如果它们的长度不同怎么办？也许一个比另一个有更多的填充。此外，除了前导之外，它们是否总是数字u？如果是这样，那么尝试安全TO_NUMBER()转换可能是值得的：
SELECT e.name, i.name
FROM employee e INNER JOIN instructor i
ON TO_NUMBER(REGEXP_SUBSTR(e.id, '\d+$')) = TO_NUMBER(REGEXP_SUBSTR(i.id, '\d+$'));
One other thing you may want to consider, however -- is there a reason for the leading uin the EMPLOYEEidcolumn? Can there be other leading characters? Does the leading ustand for something (violating first normal form, but that happens)?
但是，您可能要考虑的另一件事是——是否有理由u在EMPLOYEEid列中领先？可以有其他主角吗？领导是否u代表某事（违反第一范式，但确实发生了）？
回答by Allan
Oracle uses 1 as the base of its indexes, so substr('aaa',1,3)is equivalent to 'aaa'. You need to use 2 as the second parameter of substrin order to accomplish what you're attempting.
Oracle 使用 1 作为其索引的基数，因此substr('aaa',1,3)等效于'aaa'. 您需要使用 2 作为第二个参数substr来完成您的尝试。
Beyond that, you'd probably be better off only changing one side, if you can. If the prefix characters are consistent, you could do this:
除此之外，如果可以的话，最好只改变一侧。如果前缀字符一致，您可以这样做：
SELECT e.name, i.name
FROM employee e INNER JOIN instructor i ON REPLACE (e.id, 'u', '0') = i.id
This would potentially allow the database to use an index on instructor, which would not be possible with your solution.
这可能会允许数据库在 上使用索引instructor，而您的解决方案则无法做到这一点。
回答by Santiago P
The character position is wrong. You should start your substring on the position 2 (the first character of your string is 1 and not 0). On the other side, you are using the SUBSTR function on both string while you should use only on the employee id. Be careful, if both ids are strings, the length will be different.
人物位置不对。您应该从位置 2 开始您的子字符串（字符串的第一个字符是 1 而不是 0）。另一方面，您在两个字符串上都使用了 SUBSTR 函数，而您应该只在员工 ID 上使用。请注意，如果两个 id 都是字符串，则长度会有所不同。
If you just only want to change the 'u' on the employee id, try with the TRANSLATE function.
如果您只想更改员工 ID 上的“u”，请尝试使用 TRANSLATE 函数。
TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, they are removed from the return value.
TRANSLATE 返回 char，其中 from_string 中每个字符的所有出现都被 to_string 中的相应字符替换。char 中不在 from_string 中的字符不会被替换。参数 from_string 可以包含比 to_string 更多的字符。在这种情况下，from_string 末尾的额外字符在 to_string 中没有对应的字符。如果这些额外的字符出现在 char 中，它们将从返回值中删除。
Syntax
句法
──TRANSLATE──('char' , 'from_string' , 'to_string')──><
──TRANSLATE──('char' , 'from_string' , 'to_string')──><
Pl/SQL Example
TRANSLATE ('abcd', 'ab', '12') ==> '12cd'
TRANSLATE ('12345', '15', 'xx') ==> 'x234x'
回答by Emanuel Saringan
select e.name, i.name
from Employee e
inner join Instructor i on SUBSTR(e.id, 2, LENGTH(e.id) - 1) = SUBSTR(i.id, 2, LENGTH(i.id) - 1)
Your DB uses 1-based indexing.
您的数据库使用基于 1 的索引。
