sql - 如何在 SQL 中过滤来自 2 个不同表的数据，但另一个表几乎没有要忽略的字符串？

Question

例如：

• 一张表有 Apple、Google、Facebook 等帐户名称。
• 其他表的帐户名称为 2-apple、3-google、4-facebook。

我需要根据帐户名称组合这两个表，但希望查询忽略另一个表中的前 2 个字符串值 = 粗体值（2-苹果、3-谷歌、4-Facebook）。

标签： sql

Answer 1

记住小写以避免比较问题

with x as ( 
select 'Google' as mark from dual union all
select 'Facebook' as mark from dual 
),
y as (
select '2-apple' as other from dual union all 
select '3-google' as other from dual union all
select '4-facebook' as other from dual 
) 
select x.mark, y.other 
from x inner join y 
 on ( lower(x.mark) = lower(substr(y.other,3)) );

结果

SQL> with x as (
select 'Google' as mark from dual union all
select 'Facebook' as mark from dual
  ),
  y as (
select '2-apple' as other from dual union all
  select '3-google' as other from dual union all
select '4-facebook' as other from dual
)
   select x.mark, y.other
from x inner join y
    on ( lower(x.mark) = lower(substr(y.other,3)) );

MARK     OTHER
-------- ----------
Google   3-google
Facebook 4-facebook