-- 建测试表
CREATE TABLE DEMO ( 
 ID varchar ( 10 ) PRIMARY KEY, 
 DSC varchar ( 100 ), 
 PID varchar ( 10 ) 
);

Insert Into DEMO values ('00001', '中国', '-1');

Insert Into DEMO values ('00011', '陕西', '00001');

Insert Into DEMO values ('00012', '贵州', '00001');

Insert Into DEMO values ('00013', '河南', '00001');

Insert Into DEMO values ('00111', '西安', '00011'); 

insert Into DEMO values ('00112', '咸阳', '00011');

Insert Into DEMO values ('00113', '延安', '00011');

select * from demo;

-- SELECT * FROM start_demo start with id = '00001' Connect By Prior id = pid;
-- start with 的作用就是从当前id为 00001的通过ID=pid进行连接，并递归查询，把ID=00001的后代都查出来

-- SELECT * FROM start_demo start with id = '00113' Connect By Prior PID = id  
-- 上一条记录的PID是本条记录的ID，找祖宗


SELECT
 id, dsc, pid
FROM
 demo d1 
WHERE NOT EXISTS(
 select * from demo d2,demo d3  where d2.ID=d3.PID AND d1.ID=d2.ID
);

-- 根据当前ID找到父级的循环
WITH recursive t AS
  (
    SELECT *
    FROM demo 
    WHERE id = '00111'
    UNION ALL
    SELECT a.*
    FROM demo a, t b
    WHERE a.id = b.pid
  )
SELECT *
FROM t;

-- 根据当前PID找到所有子级的循环
WITH recursive t AS
  (
    SELECT *
    FROM demo 
    WHERE pid = '00001'
    UNION ALL
    SELECT a.*
    FROM demo a, t b
    WHERE a.pid = b.id
  )
SELECT *
FROM t;

-- 根据当前ID找到所有子级的循环
WITH recursive t AS
  (
    SELECT *
    FROM demo 
    WHERE id = '00011'
    UNION ALL
    SELECT a.*
    FROM demo a, t b
    WHERE a.pid = b.id
  )
SELECT *
FROM t;