数据结构
网络
关系数据库管理系统
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
服装设计
法律研究如何在 Oracle 中遍历分层数据并在层次结构中对同一级别的行进行排序？
问题陈述：您需要自上而下遍历层次结构数据，并在层次结构中对同一级别的行进行排序。
解决方案：通常的 ORDER BY 子句不会对同一层次结构级别的行进行排序。我们需要在 ORDER BY 子句中使用 SIBLINGS。
此外，Oracle 提供 CONNECT BY 子句来指定分层查询，即如何连接父节点和子节点，以及 PRIOR 运算符来定义父节点之间的连接条件，以及 LEVEL 伪列来指示当前行与根/父行的距离。
此外，我们可以使用 START WITH 子句来指示从哪里开始树导航。我们必须使用 PRIOR 运算符来指定父行中与子行中的链接列具有相同值的列。
为了循环遍历整个层次结构，请删除 START WITH 子句。这将获取所有学生及其教授、教授的教授（即院长）等，直到机构的最高管理人员/主管。
示例 1：在教授 ID 为 102 的父行中对同一层次结构级别的行进行排序 SQL
/*
Function - Identify the hierarchical/structure for the professor_id = 102
Tables Used - students
Data - documented below
*/
SELECT student_id,
level,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id = 102
CONNECT BY professor_id = PRIOR student_id
ORDER SIBLINGS by first_name, last_name;输出
103 1 BROWN MICHAEL 106 2 -DAVIS RICHARD 107 2 -GARCIA CHARLES 104 2 -JONES WILLIAM 105 2 -MILLER DAVID
示例 2：在教授 ID 为空的父行中对同一层次结构级别的行进行排序 SQL
/*
Function - Sort the rows at the same level in a hierarchy with in a Parent row
Tables Used - students
Data - documented below
*/
SELECT student_id,
level,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id IS null
CONNECT BY professor_id = PRIOR student_id
ORDER SIBLINGS by first_name, last_name;输出
100 1 SMITH JAMES 101 2 -JOHNSON JOHN 108 3 -RODRIGUEZ JOSEPH 111 4 -ANDERSON DANIEL 110 4 -MARTINEZ CHRISTOPHER 112 4 -TAYLOR PAUL 113 4 -THOMAS MARK 109 4 -WILSON THOMAS 102 2 -WILLIAMS ROBERT 103 3 -BROWN MICHAEL 106 4 -DAVIS RICHARD 107 4 -GARCIA CHARLES 104 4 -JONES WILLIAM 105 4 -MILLER DAVID
排序显然发生在层次结构的每个级别。输出显示在 SMITH JAMES 下，直接下属是 JOHNSON JOHN 和 WILLIAMS ROBERT，按字母顺序排列在第 2 级。在 JOHNSON JOHN 下，我们在第 3 级有进一步的下属，按字母顺序完全排序。
数据准备：用于问题的示例数据如下所示。数据完全是为了演示目的而编造的。
示例
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
示例
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ; INSERT INTO students VALUES (100,'SMITH','JAMES','[email protected]','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL); INSERT INTO students VALUES (101,'JOHNSON','JOHN','[email protected]','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90); INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','[email protected]','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90); INSERT INTO students VALUES (103,'BROWN','MICHAEL','[email protected]','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60); INSERT INTO students VALUES (104,'JONES','WILLIAM','[email protected]','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60); INSERT INTO students VALUES (105,'MILLER','DAVID','[email protected]','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60); INSERT INTO students VALUES (106,'DAVIS','RICHARD','[email protected]','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60); INSERT INTO students VALUES (107,'GARCIA','CHARLES','[email protected]','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60); INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','[email protected]','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100); INSERT INTO students VALUES (109,'WILSON','THOMAS','[email protected]','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100); INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','[email protected]','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100); INSERT INTO students VALUES (111,'ANDERSON','DANIEL','[email protected]','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100); INSERT INTO students VALUES (112,'TAYLOR','PAUL','[email protected]','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100); INSERT INTO students VALUES (113,'THOMAS','MARK','[email protected]','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100); COMMIT;
625 次查看
