我是SQL菜鸟,我想知道是否有人可以给我 对以下问题有一些帮助: 所以我有两个表格"学校"和"教师"。 "学校"表的"search_key"列是一个大字符串, 组合教师姓名和其他元素(例如:"ENGLISH | JANE | [90, 56]
( 所以我要做的是匹配"name"列中的字符串 与前一个表的"教师"表,获取具有匹配项的单元格。
SELECT * FROM(
SELECT substr(a.search_key, 6, instr(a.search_key, '|'))
FROM schools a
) JOIN teachers s ON a.search_key = s.search_key
这就是我一直在尝试做的事情,子串和尝试匹配,但到目前为止没有运气。 有什么想法吗?
我不确定为什么它会比:
SELECT *
FROM schools s
INNER JOIN teachers t ON t.teacher_name LIKE '%' + s.search_key + '%'
这应该让你开始 -
WITH SCHOOLS AS (
SELECT
'ENGLISH | JANE | [90,56]' AS SEARCH_KEY
FROM
DUAL
),TEACHER AS (
SELECT
'JANE' AS NAME
FROM
DUAL
) SELECT
S.SEARCH_KEY
FROM
SCHOOLS S,
TEACHER T
WHERE
S.SEARCH_KEY LIKE '%' || T.NAME || '%';
输出-
SEARCH_KEY
ENGLISH | JANE | [90, 56]
另一种方法是——
WITH SCHOOLS AS (
SELECT
'ENGLISH | JANE | [90,56]' AS SEARCH_KEY
FROM
DUAL
),TEACHER AS (
SELECT
'JANE' AS NAME
FROM
DUAL
) SELECT
S.SEARCH_KEY
FROM
SCHOOLS S,
TEACHER T
WHERE
TRIM(REGEXP_SUBSTR(S.SEARCH_KEY,'(S*)(W)',1,3)) = T.NAME;
如果名称始终是SEARCH_KEY受 |