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

arrow
arrow
    全站熱搜

    hedgezzz 發表在 痞客邦 留言(0) 人氣()