sql - 如何在子查询中使用 LIKE 通配符？

Question

Table: FirstNames
NAME
    Tom
    Joe
    Peter

Table: FullNames
FULL_NAME:
    Tom Petty.
    Joe Satriani.
    Peter Griffin.
    Sarah Connor.

我想运行一个查询:

select * 
from FullNames where FULL_NAME like '%' || (select NAME from FirstNames) || '%'

它产生:

ORA-01427: single-row subquery returns more than one row

这似乎是正确的。有没有办法在 Oracle 中做到这一点？

Answer 1

你可以使用 JOIN:

SELECT * 
FROM FullNames f
JOIN FirstNames g
  ON f.FULL_NAME LIKE '%' || g.NAME || '%';