我想找出一组数(如100,12,456...)在下面这个表的A字段中,出现过的数。并且同时得到它的B字段的值。 A B
1 111,234,123 31
2 12,32,456 32
3 456,12,1 33
结果形如:111 31
234 31
12 33
.......我想过用聚合函数,但得不到B字段得值,还请各位前辈赐教了。谢谢这里也提问了:http://community.csdn.net/Expert/topic/5548/5548271.xml?temp=.1792108
1 111,234,123 31
2 12,32,456 32
3 456,12,1 33
结果形如:111 31
234 31
12 33
.......我想过用聚合函数,但得不到B字段得值,还请各位前辈赐教了。谢谢这里也提问了:http://community.csdn.net/Expert/topic/5548/5548271.xml?temp=.1792108
create table tab2(id integer);
insert into tab1 values('111,234,123',31);
insert into tab1 values('12,32,456',32);
insert into tab1 values('456,12,1',33);
insert into tab2 values(111);
insert into tab2 values(234);
insert into tab2 values(12);
commit;
SQL> select id,b from tab1,tab2 where instr(to_char(','||a||','),to_char(','||id||','))>0; ID B
---------- ----------
111 31
234 31
12 32
12 33
这样可以吗?
create table tab1(a varchar2(20),b integer);
create table tab2(id integer);
insert into tab1 values('111,234,123',31);
insert into tab1 values('12,32,456',32);
insert into tab1 values('456,12,1',33);
insert into tab2 values(111);
insert into tab2 values(234);
insert into tab2 values(12);
commit;
SQL> select id,b from tab1,tab2 where instr(to_char(','||a||','),to_char(','||id||','))>0; ID B
---------- ----------
111 31
234 31
12 32
12 33