oracle层次查询-查询每个学生所选择的课程列表（同一行显示）

还拿学生课程表来说明如下，表结构如下：

[sql] 
create table T_STU  
(  
  id   NUMBER not null,  
  name VARCHAR2(255)  
)  
  
create table T_COURSE  
(  
  id         NUMBER not null,  
  coursename VARCHAR2(255)  
)  
  
create table T_S_C  
(  
  sid    NUMBER,  
  cid    NUMBER,  
  course NUMBER  
)

数据如下：

经过关联之后，如下：

需求为达到以下效果

即显示每个学生选择的课程列表，在同一行显示。

所以sql为以下：

[sql] 
with tab1 as   
(select s.id,s.name,c.coursename,sc.course  
from t_stu s,t_course c,t_s_c sc  
where s.id=sc.sid and c.id=sc.cid)  
select z.name,  
       level lv,  
        
       SYS_CONNECT_BY_PATH(z.coursename, '/') text,  
        CONNECT_BY_ROOT (z.coursename) root  
     from (  
select t.*,Row_number() OVER (PARTITION BY id ORDER BY id) r  
from tab1 t ) z  
     where connect_by_isleaf = 1  
           start with r = 1   
           connect by r - 1 = prior r    and id = prior id

效果如下：

说明：

1、Row_number() OVER (PARTITION BY COL1 ORDER BY COL2)

表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

 select t.* ,ROW_NUMBER() OVER(ORDER BY sid) x from t_s_c t
 select t.* ,ROW_NUMBER() OVER(PARTITION BY t.sid ORDER BY t.course) x from t_s_c t

2、with table as [,table2 as]

相当于建个临时表（用于一个语句中某些中间结果放在临时表空间的SQL语句），Oracle 9i 新增WITH语法，可以将查询中的子查询命名，放到SELECT语句的最前面。

  with tab1 as
  (select * from t_s_c where course >=80)
  select * from tab1

3、connect by

重点函数：sys_connect_by_path(z.coursename, '/')

z.coursename表示要连接的字段，‘/’表示连接符

使用这个方法之前必须在where条件中构建树

where start with 条件1 connect by prior 条件2

条件1 ：表示起始条件，例如，起始条件为组内排序后的rownum为1

条件2 ：表示要连接的下一行与上一行的关系

prior.列名：代表上一行的列

Level是层次查询的一个伪列，即深度

SYS_CONNECT_BY_PATH ：实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来

CONNECT_BY_ROOT：它用在列名之前用于返回当前层的根节点

CONNECT_BY_ISCYCLE 查询叶子节点为1，如果：

Start with确定将哪些行作为root，如果没有start with,则每行都当作root，然后查找其后代。如图：