表A(id,name)
1 tom
2 jim
3 zz
21 tt
41 ss 表B(id,relation)
1 1,3
2 2,41 表B中的relation存的是A表中的id值的组合,A,B表中id均为int型的主键 期望获得表A的结果为:
id name
2 jim 即期望获得在A表中存在而在B表中的relation列中不存在的记录
1 tom
2 jim
3 zz
21 tt
41 ss 表B(id,relation)
1 1,3
2 2,41 表B中的relation存的是A表中的id值的组合,A,B表中id均为int型的主键 期望获得表A的结果为:
id name
2 jim 即期望获得在A表中存在而在B表中的relation列中不存在的记录
select a.*
from a,b
where instr(b.relation,a.id)=0;
如果是希望找出b表relation列中,出现的a表的id号的人:
SQL> with a as(
2 select 1 id,'tom' name from dual union all
3 select 2,'jim' from dual union all
4 select 3,'zz' from dual union all
5 select 21,'tt' from dual union all
6 select 41,'ss' from dual)
7 ,b as(
8 select 1 id,'1,3' relation from dual union all
9 select 2,'2,41' from dual)
10 select distinct(a.id),a.name
11 from a,b
12 where b.relation like '%'||a.id||'%'; ID NAME
---------- ----
1 tom
2 jim
3 zz
41 ss
--
id name
2 jim 即期望获得在A表中存在而在B表中的relation列中不存在的记录--结果应该是:
id name
21 tt
期望结果应该是 21,tt,上面的写错了
是的,上面写错了是要得到这个结果:
id name
21 tt
select substr(relation,1,instrb(relation,',',1) - 1) from b where id= '2')
with a as(
select 1 id,'tom' name from dual
union all
select 2,'jim' from dual
union all
select 3,'zz' from dual
union all
select 21,'jim' from dual
union all
select 41,'ss' from dual
),b as(
select 1 id,'1,3' relation from dual
union all
select 2,'2,41' from dual
)
select *
from a
where not exists
(select 1
from b
where instr(',' || relation || ',', ',' || a.id || ',') > 0)
where id not in(select relation from b);