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

来源:互联网 发布:大数据技术具体应用 编辑:程序博客网 时间:2024/03/29 06:08

      还拿学生课程表来说明如下,表结构如下:

  

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.coursefrom t_stu s,t_course c,t_s_c scwhere 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) rfrom 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,然后查找其后代。如图:

 

 

 

 

 

 

 

原创粉丝点击