解决方案 »
- oracle 触发器问题?
- 亿级海量数据中模糊查询优化问题,请高手指点。
- 数据库说明书中,下面两点应该写什么内容?看来看去不知道写什么内容,那位高人指点一下,最好有列子
- 关于动态游标的问题
- Oracle OLAP 9.0.1.0.1 服务启动提示:“在本地计算机无法启动Oracle OLAP 9.0.1.0.1服务。错误3:系统找不到指定路径。”
- 使用sql loader 一行数据 如何插入多表?
- 为什么数据库自带的utlsmtp.sql无法执行 报ora-00900错误
- update 奇怪的问题
- 请教sql* plus 问题
- oracel中我已经用sum累加出了和,如何输出该信息的累加
- 求oracle sql好书
- 关于SQL语句优化
select c.cid,listagg(s.sname) within group (order by c.cid) as snames from course c , student s where instr(c.sidstr,s.sid)>0 group by c.cid
from (select s.sname,
c.cid,
row_number() over(partition by c.sidstr order by s.sid) as rn
from course c, student s
where instr(c.sidstr, s.sid) > 0) vs
start with vs.rn = 1
connect by prior vs.rn = vs.rn - 1
and prior vs.cid = vs.cid
group by vs.cid
order by vs.cid
from
(SELECT cid,REGEXP_SUBSTR(sidStr,'[^,]+',1,l) AS sid
FROM course
,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100)
WHERE l <=LENGTH(sidStr) - LENGTH(REPLACE(sidStr,','))+1
ORDER BY 1,2) a left join student b
on a.sid=b.sid
group by a.cid
FROM COURSE C, STUDENT S
WHERE INSTR(C.SIDSTR, S.SID) > 0
GROUP BY C.CID10g以下版本,4楼的比较好吧
with course as(
select '1' cid,'1,2,3,4' sidStr from dual union
select '2','1,2,3' from dual),
student as(
select '1' sid,'学生1' sname from dual union
select '2','学生2' from dual union
select '3','学生3' from dual union
select '4','学生4' from dual)select distinct wm_concat(sname) over(partition by sidstr)
from course,student
where instr(','||course.sidstr||',',','||student.sid||',') > 0
2.for循环搞定之。