1.表1
sid code1 code2
1 1,2 12345
2 2,3 12345
3 1 12345
2.表2
subid name code
1 physics 12345
2 maths 12345
3 biology 12345
4 geography 12345要把表1的字段按逗号字符分开,然后匹配表2的对应subid的name,匹配到多个的话就用逗号隔开,表1 的code2和表2的code要匹配。
想得到的结果如下:
sid code1 code2
1 physics,maths 12345
2 maths,biology 12345
3 physics 12345希望各位大侠能帮忙设计一下SQL语句,谢谢!!
sid code1 code2
1 1,2 12345
2 2,3 12345
3 1 12345
2.表2
subid name code
1 physics 12345
2 maths 12345
3 biology 12345
4 geography 12345要把表1的字段按逗号字符分开,然后匹配表2的对应subid的name,匹配到多个的话就用逗号隔开,表1 的code2和表2的code要匹配。
想得到的结果如下:
sid code1 code2
1 physics,maths 12345
2 maths,biology 12345
3 physics 12345希望各位大侠能帮忙设计一下SQL语句,谢谢!!
SID CODE1 CODE2
-------------------- -------------------- --------------------
1 1,2 12345
2 2,3 12345
3 1 12345
SQL> select * from test2;
SUBID NAME CODE
-------------------- -------------------- --------------------
1 physics 12345
2 maths 12345
3 biology 12345
4 geography 12345
SQL>
SQL> SELECT a.sid,wm_concat(b.name),a.code2
2 FROM test1 a,test2 b
3 WHERE instr(','||a.code1||',',','||b.subid||',')>0
4 GROUP BY a.sid,a.code2;
SID WM_CONCAT(B.NAME) CODE2
-------------------- -------------------------------------------------------------------------------- --------------------
1 physics,maths 12345
2 maths,biology 12345
3 physics 12345
SQL>
SQL> select * from test1;
SID CODE1 CODE2
-------------------- -------------------- --------------------
1 1,2 12345
2 2,3 12345
3 1 12345
SQL> select * from test2;
SUBID NAME CODE
-------------------- -------------------- --------------------
1 physics 12345
2 maths 12345
3 biology 12345
4 geography 12345
SQL>
SQL> SELECT a.sid,wm_concat(b.name),a.code2
2 FROM test1 a,test2 b
3 WHERE instr(','||a.code1||',',','||b.subid||',')>0
4 GROUP BY a.sid,a.code2;
SID WM_CONCAT(B.NAME) CODE2
------------ ----------------------- --------------------
1 physics,maths 12345
2 maths,biology 12345
3 physics 12345
SQL>
不过貌似还有些不完整,因为test2中的code存在不同记录,所以需要加上where条件
and a.code2='xxxxx' and a.code2 = b.code
from a,
(select sys_connect_by_path(subid, ',') nujn,
sys_connect_by_path(name, ',') tpsjn
from (select rownum rn, subid, name from b) b
connect by prior rn < rn) c
where replace(c.nujn, ',') = replace(code, ',')
order by sid;
加上你的
and a.code2='xxxxx' and a.code2 = b.code
SQL> SELECT a.sid,wm_concat(b.name),a.code2
2 FROM test1 a,test2 b
3 WHERE instr(','||a.code1||',',','||b.subid||',')>0
4 AND a.code2=b.code
5 GROUP BY a.sid,a.code2;
SID WM_CONCAT(B.NAME) CODE2
-------------------- -------------------------------------------------------------------------------- --------------------
1 physics,maths 12345
2 maths,biology 12345
3 physics 12345
SQL>
感觉两位大虾的解答,结贴!
我的是枚举后完整比对
因为我的原理是 1 2 3 几个数构造可能的排列
然后和2中的对比
所以是个精确的匹配
你如果要那样的话 1楼可以应该
因为他的是用 instr判断包含关系的 有没有应该没影响