CREATE TABLE employee(
empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
salary DECIMAL(9, 2),
mgrid INTEGER
);
INSERT INTO employee VALUES ( 1, 'Jones', 30000, 10);
INSERT INTO employee VALUES ( 2, 'Hall', 35000, 10);
INSERT INTO employee VALUES ( 3, 'Kim', 40000, 10);
INSERT INTO employee VALUES ( 4, 'Lindsay', 38000, 10);
INSERT INTO employee VALUES ( 5, 'McKeough', 42000, 11);
INSERT INTO employee VALUES ( 6, 'Barnes', 41000, 11);
INSERT INTO employee VALUES ( 7, 'O''Neil', 36000, 12);
INSERT INTO employee VALUES ( 8, 'Smith', 34000, 12);
INSERT INTO employee VALUES ( 9, 'Shoeman', 33000, 12);
INSERT INTO employee VALUES (10, 'Monroe', 50000, 15);
INSERT INTO employee VALUES (11, 'Zander', 52000, 16);
INSERT INTO employee VALUES (12, 'Henry', 51000, 16);
INSERT INTO employee VALUES (13, 'Aaron', 54000, 15);
INSERT INTO employee VALUES (14, 'Scott', 53000, 16);
INSERT INTO employee VALUES (15, 'Mills', 70000, 17);
INSERT INTO employee VALUES (16, 'Goyal', 80000, 17);
INSERT INTO employee VALUES (17, 'Urbassek', 95000, NULL);
SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
FROM employee
START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid;
empid name mgrid leaf
16 Goyal 17 0
14 Scott 16 1
12 Henry 16 0
9 Shoeman 12 1
8 Smith 12 1
7 O'Neil 12 1
11 Zander 16 0
6 Barnes 11 1
5 McKeough 11 1
SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
FROM employee
START WITH name = 'Henry'
CONNECT BY PRIOR empid = mgrid;
empid name mgrid leaf
12 Henry 16 0
9 Shoeman 12 1
8 Smith 12 1
7 O'Neil 12 1
SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
FROM employee
START WITH name = 'Goyal'
CONNECT BY empid = prior mgrid;
empid name mgrid leaf
16 Goyal 17 0
17 Urbassek 1
SELECT empid, name, mgrid , CONNECT_BY_ISLEAF leaf
FROM employee
START WITH name = 'Henry'
CONNECT BY empid = prior mgrid;
empid name mgrid leaf
12 Henry 16 0
16 Goyal 17 0
17 Urbassek 1
SELECT empid,name, LEVEL FROM employee START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid;
empid name level
16 Goyal 1
14 Scott 2
12 Henry 2
9 Shoeman 3
8 Smith 3
7 O'Neil 3
11 Zander 2
6 Barnes 3
5 McKeough 3
SELECT empid,name, connect_by_root name as topname
FROM employee START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid;
empid name topname
16 Goyal Goyal
14 Scott Goyal
12 Henry Goyal
9 Shoeman Goyal
8 Smith Goyal
7 O'Neil Goyal
11 Zander Goyal
6 Barnes Goyal
5 McKeough Goyal
SELECT empid,name, connect_by_root empid as topempid
FROM employee START WITH name = 'Goyal'
CONNECT BY PRIOR empid = mgrid;
empid name topempid
16 Goyal 16
14 Scott 16
12 Henry 16
9 Shoeman 16
8 Smith 16
7 O'Neil 16
11 Zander 16
6 Barnes 16
5 McKeough 16
- Nov 01 Fri 2013 16:59
Hierarchy
全站熱搜
留言列表