当前位置:编程问答 > Oracle >

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

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,然后查找其后代。如图:
 
 
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到什么编程难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,