select ci.ci_id ci_id, listagg(stu.stu_name,',') stu_name

from pm_ci ci,pm_stu stu 

where instr(ci.stu_ids,stu.stu_id) > 0

group by ci.ci_id

order by ci.ci_id

select ci_id,MAX(M) from (

select ci_id,wm_concat(stu_name) over (partition by c.ci_id order by s.stu_id)m from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0)e group by e.ci_id;

为啥我用这个会报错呢？listagg是个什么函数，没看懂

1、先创建表及初始化数据

 ①创建及初始化学生表PM_STU

create table PM_STU(STU_ID           VARCHAR2(20) NOT NULL,

                                  STU_NAME      VARCHAR2(20));                

INSERT INTO  PM_STU VALUES(1,'张三');

INSERT INTO  PM_STU VALUES(2,'李四');

INSERT INTO  PM_STU VALUES(3,'王五');

INSERT INTO  PM_STU VALUES(4,'赵六'); 

②创建及初始化选课表PM_CI

•   先创建一个PM_CI1来转换成PM_CI

CREATE TABLE PM_CI1(CI_ID        VARCHAR2(20) NOT NULL,

                                      STU_IDS   VARCHAR2(20));                 

INSERT INTO PM_CI1 VALUES(1,1);

INSERT INTO PM_CI1 VALUES(1,2);

INSERT INTO PM_CI1 VALUES(1,3);

INSERT INTO PM_CI1 VALUES(1,4);

INSERT INTO PM_CI1 VALUES(2,1);

INSERT INTO PM_CI1 VALUES(2,4);

• 创建PM_CI，并插入数据

CREATE TABLE PM_CI(CI_ID       VARCHAR2(20) NOT NULL,

                                   STU_IDS   VARCHAR2(20));

INSERT INTO  PM_CI  SELECT CI_ID,WM_CONCAT(STU_IDS)  FROM PM_CI1 GROUP BY CI_ID;

---先做出两张表的笛卡尔积，再根据有效的条件instr(a.STU_IDS,b.STU_ID) !=0，过滤得到每个科目对应的学生

       select  a.CI_ID ,b.STU_NAME

        from  PM_CI a,PM_STU b

        where   instr(a.STU_IDS,b.STU_ID) !=0;

  ---使用WM_CONCAT（）函数，列转行

select   CI_ID,   WM_CONCAT(STU_NAME) SUT_NAME

from (

        select a.CI_ID ,b.STU_NAME

        from PM_CI a,PM_STU b

        where instr(a.STU_IDS,b.STU_ID) !=0

         )

group by CI_ID;

select p.ci_id,max(stu_name) as STU_NAME
from (select c.ci_id,wm_concat(s.stu_name) 
over(partition BY c.stu_ids order by s.stu_id) as stu_name
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id) > 0) p 
group by p.ci_id

查询出来后有排序效果的..

你这个怕是表里面加入了其他数据就要挂啊

把STU_NAMES用to_char转一下，to_char(WM_CONCAT(STU_NAME) )。应该是oracle高版本得到的WM_CONCAT结果默认是CLOB类型，用to_char转一下就能看到值了

select ci_id,wm_concat(stu_name) stu_name    

from pn_ci a,pm_stu b

where instr(stu_ids,stu_id)>0 

group by ci_id;

自己create一个

instr不是组函数吧

replace(tablename,charA,newcharA)

select c.ci_id,wm_concat(s.stu_name)
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id)>0
group by ci_id；

select ci.ci_id CID,wm_concat(case when instr(stu_ids,stu.stu_id) > 0 then stu.stu_name end) SNAME
from pm_ci ci,pm_stu stu
group by ci.ci_id

你问的是什么鬼？

最后一道练习题大概是这样，但是wm_concat没有排序 这里我也没有处理到

精简：

SELECT c.ci_id ,wm_concat(s.stu_name)  
  FROM pm_ci c , pm_stu s 
  WHERE INSTR(c.stu_ids,s.stu_id)>0
  GROUP BY c.ci_id;

得到的结果是一样的。