现有2张表,其中B表AIDS字段保存的是A表的ID集合
A B
id name ID AIDS
1 aa 1 1,2
2 bb 2 2,3
3 cc 3 1,2,3
4 dd 现在我知道一个B表的id,要查询A表中ID在此ID对应的AIDS中的记录结合,查询语句咋一步写出来,谢谢各位
A B
id name ID AIDS
1 aa 1 1,2
2 bb 2 2,3
3 cc 3 1,2,3
4 dd 现在我知道一个B表的id,要查询A表中ID在此ID对应的AIDS中的记录结合,查询语句咋一步写出来,谢谢各位
id name
1 aa
2 bb
3 cc
4 ddBid aids1 1,2
2 2,3
3 1,2,3
drop table a;
create table a (id number primary key, name varchar2(20));drop table b;
create table b (id number primary key, aids varchar2(2000));insert into a values (1, 'aa');
insert into a values (2, 'bb');
insert into a values (3, 'cc');
insert into a values (4, 'dd');
insert into b values (1, '1,2');
insert into b values (2, '2,3');
insert into b values (3, '1,2,3');
commit;select * from a
where instr(',' || (select aids from b where id = 2) || ',', ',' || id || ',') != 0; ID NAME
---------- ------------------------------
2 bb
3 cc
create table ta(id int,name varchar2(10));
insert into ta select 1,'aa' from dual;
insert into ta select 2,'bb' from dual;
insert into ta select 3,'cc' from dual;
insert into ta select 4,'dd' from dual;create table tb(id int,aids varchar2(10));
insert into tb select 1,'1,2' from dual;
insert into tb select 2,'2,3' from dual;
insert into tb select 3,'1,2,3' from dual;
select * from ta a,tb b
where instr(','||b.aids||',',','||a.id||',')>0
SELECT '1' id,'aa' name FROM dual
UNION ALL
SELECT '2', 'bb' FROM dual
UNION ALL
SELECT '3', 'cc' FROM dual
UNION ALL
SELECT '4', 'dd' FROM dual
),
B AS(
SELECT '1'id,'1,2' aids FROM dual
UNION ALL
SELECT '2' ,'2,3' FROM dual
UNION ALL
SELECT '3' ,'1,2,3' FROM dual
)
select * from a
where instr(',' || (select aids from b where id = 3) || ',', ',' || id || ',') > 0;结果:
1 aa
2 bb
3 cc
--knowlege : instr, substr ,union
---table----
create table A (id number, name varchar2(2));
insert into A values(1,'aa')
insert into A values(2,'bb')
insert into A values(3,'cc')
insert into A values(4,'dd')
create table B(id number, aids varchar2(10));
insert into B values (1,'1,2')
insert into B values(2,'2,3')
insert into B values(3,'1,2,3')--sqlstatement----
select * from A where id in
(select a.id1 from
(
select substr(aids,instr(aids,'1','1')-1,1 )+0 id1
from B where id=2
union
select substr(aids,instr(aids,'2','1'),1 )+0 id1
from B where id=2
union
select substr(aids,instr(aids,'3','1'),1 )+0 id1
from B where id=2) a )
--result--
/*
2 bb
3 cc
*/
where instr(',' || (select aids from b where id = 3) || ',', ',' || id || ',') > 0;
和select * from ta a,tb b
where b.aids=3 and instr(','||b.aids||',',','||a.id||',')>0效果应该一样吧?另外谁可以讲讲instr(','||b.aids||',',','||a.id||',')>0,小弟不是很懂^ 是不是
','||b.aids||','是一部分,','||a.id||','这是一部分,中间有个,号?
跟这个菜式一样的:select * from a, b
where b.id=3 and instr(','||b.aids||',',','||a.id||',')>0;instr()函数详解
测试的时候用d做表,这个才对。