oracle 一个字段存多个ID(以逗号","分割),并用该字段与其他表的ID关联

核心

对两张表的关联字段左右两边同时加上分隔符，然后使用instr()函数判断是否存在

instr(','||class_student.student_id||',' , ','||student.student_id||',') > 0

数据

student(学生表)

学生编号	学生姓名
1	张三
2	李四
3	王五

class_student(班级学生关系表)

班级编号	学生编号
1	1,2
2	3
3	4

应用

1. 标题查询学生所在班级（一个字段存多个ID(以逗号","分割),并用该字段与其他表的ID关联）

with student as(--学生表
 select '1' student_id,'张三' student_name from dual union
 select '2' student_id,'李四' student_name from dual union
 select '3' student_id,'王五' student_name from dual
),class_student as(--班级学生关系表
 select '1' as class_id,'1,2' student_id from dual union
 select '2' as class_id,'3' student_id from dual union
 select '3' as class_id,'4' student_id from dual 
)select 
   student.student_id,
   class_student.class_id
from student 
 join class_student on instr(','||class_student.student_id||',' , ','||student.student_id||',') > 0

结果展示

学生编号	班级编号
1	1
2	1
3	2

2. 获取每个班级学生对应姓名（将存多个ID(以逗号","分割)的字段转化成对应中文含义）

with student as(--学生表
select '1' student_id,'张三' student_name from dual union
select '2' student_id,'李四' student_name from dual union
select '3' student_id,'王五' student_name from dual
),class_student as(--班级学生关系表
select '1' as class_id,'1,2' student_id from dual union
select '2' as class_id,'3' student_id from dual union
select '3' as class_id,'4' student_id from dual 
)select 
class_student.class_id,
(select listagg(student.student_name, ',') within group(order by student.student_id) from student where instr(','||class_student.student_id||',' , ','||student.student_id||',') > 0) student_name
from class_student 

结果展示

班级编号	学生姓名
1	张三,李四
2	王五
3