我有如下记录
Table1
id | 123 456 345 789
Table2
name| 123.abc 345.jkl
现在我想只显示id,即(同样456,789
)
请寻求帮助
您可以使用MINUS
.如果id
VARCHAR2
字段,则不需要在 id 上TO_CHAR
。 SUBSTR
,INSTR
用于提取第一个"."
SELECT TO_CHAR(id) ID
FROM table1
MINUS
SELECT SUBSTR(NAME, 1, INSTR(NAME, '.') - 1)
FROM table2 ;
也许是这样的。"匹配"在子查询中完成(在 WHERE 子句的 NOT EXISTS 条件中)。它假设要求是"name"与"id"匹配,而"name"是"id",后跟一个句点,后跟至少一个字符。如果句点不是必需的,或者如果它是必需的,但它可能是名称的最后一个字符,则可以轻松修改查询以适应该要求。
with
table1 ( id ) as (
select '123' from dual union all
select '456' from dual union all
select '345' from dual union all
select '789' from dual
),
table2 ( name ) as (
select '123.abc' from dual union all
select '345.jkl' from dual
)
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select id
from table1 t1
where not exists (
select name
from table2
where name like t1.id || '._%'
)
;
ID
---
456
789