資料結構
網路
關係資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
服裝設計
法律研究如何在 Oracle 中識別層次結構表中不是任何其他行的父行的行？
問題陳述：如何在層次結構表中識別葉子行，即不是任何其他行的父行的行。
解決方案：Oracle 提供 CONNECT_BY_ISLEAF 子句來識別不是任何其他行的父行的行。首先讓我們看看 connect_by_isleaf 是如何工作的。
SQL
/*
Function - Example to show if the row is parent of any other rows or not
Tables Used - students Data - Documented below
*/
SELECT student_id,
level,
CASE WHEN connect_by_isleaf = 0
THEN 'Yes'
ELSE 'No'
END AS is_parent,
lpad('-',(level-1)*3) || first_name || ' ' || last_name AS full_name
FROM students
START WITH professor_id IS null
CONNECT BY PRIOR student_id = professor_id;輸出：上面 SQL 的幾行
100 1 Yes SMITH JAMES 101 2 Yes -JOHNSON JOHN 108 3 Yes -RODRIGUEZ JOSEPH 109 4 No -WILSON THOMAS 110 4 No -MARTINEZ CHRISTOPHER 111 4 No -ANDERSON DANIEL 112 4 No -TAYLOR PAUL 113 4 No -THOMAS MARK 102 2 Yes -WILLIAMS ROBERT 103 3 Yes -BROWN MICHAEL 104 4 No -JONES WILLIAM 105 4 No -MILLER DAVID
在上面的 SQL 中，connect_by_isleaf 的值 0 表示該行是父行並具有更多子行，而值 1 表示葉子節點/記錄，它不是任何其他行的父行。
connect_by_isleaf 的一個有趣的實現是識別不是教授/管理員的學生。以下 SQL 將顯示如何實現它。
SQL
/* Function - Example to identify students that are not professors/adminstrators Tables Used - students Data - Documented below */ SELECT student_id,first_name || ' ' || last_name AS student_name, connect_by_root first_name || ' ' || last_name AS manager_name FROM students WHERE connect_by_isleaf = 1 START WITH professor_id IS null CONNECT BY PRIOR student_id = professor_id;
輸出：上面 SQL 的幾行
109 WILSON THOMAS SMITH THOMAS 110 MARTINEZ CHRISTOPHER SMITH CHRISTOPHER 111 ANDERSON DANIEL SMITH DANIEL 112 TAYLOR PAUL SMITH PAUL 113 THOMAS MARK SMITH MARK 104 JONES WILLIAM SMITH WILLIAM 105 MILLER DAVID SMITH DAVID 106 DAVIS RICHARD SMITH RICHARD 107 GARCIA CHARLES SMITH CHARLES
資料準備：用於該問題的資料如下所示。資料完全是為了演示目的而編造的。
示例
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','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
208 次檢視
