问题陈述：如何识别层次结构表中的叶行，即不是其他任何行的父级的行。
解决方案： Oracle提供了CONNECT_BY_ISLEAF子句来标识不是其他任何行的父级的行。首先，让我们看看connect_by_isleaf是如何工作的。
/*
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;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将显示如何实现它。
/* 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;
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;