假设有两张表,student表和course表,student有字段主键id、姓名name、所选课程couse_ids(课程id之间用逗号分开),course有字段主键id,课程名称name,
课程course表:
学生student表:
现在,要查询每个学生选择的课程:
最终结果:
所用sql:
select b.id 学生id, b.name 学生姓名, b.aa 学生选课id, c.id 课程id, c.name 课程名称 from
(select t.*, a.*, /*instr(t.ids,',',1,a.lv), instr(t.ids,',',1,a.lv+1),*/ t.ids as aa,
substr(t.ids, instr(t.ids,',',1,a.lv)+1, instr(t.ids,',',1,a.lv+1)-instr(t.ids,',',1,a.lv)-1) as cid
from
(select m.id, m.name, ','||m.course_ids||',' as ids from student m) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 5) a ) b, course c
where b.cid = c.id and b.cid is not null
order by b.id