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

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

  

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为以下：

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，然后查找其后代。如图：