SQL 递归

SELECT T.EMPNO
        ,T.MGR
        ,T.JOB
FROM EMP T ;

    7566 项目经理  1  
7902   7788 小组长 2
7369   7876 搬砖的 3

---树状查询  递归查询

---PRIOR 侧的字段是 EMPNO，就是往下属寻找
------往 子项 找---------------------------
SELECT T.EMPNO
       ,T.MGR
       ,T.ENAME
       ,LEVEL 
       ,SYS_CONNECT_BY_PATH(T.ENAME,'>')
FROM EMP T
START WITH T.EMPNO = 7566 ---表示从哪些数据开始找
CONNECT BY PRIOR T.EMPNO = T.MGR;  

------------往 父项 找---------------------  
SELECT T.EMPNO
       ,T.MGR
       ,T.ENAME
       ,LEVEL 
       ,SYS_CONNECT_BY_PATH(T.ENAME,'=>')
FROM EMP T
START WITH T.EMPNO = 7566 ---表示从哪些数据开始找
CONNECT BY PRIOR T.MGR = T.EMPNO;  

---练习

CREATE TABLE SC_DISTRICT
(
  ID         NUMBER(10)                  NOT NULL,
  PARENT_ID  NUMBER(10),
  NAME       VARCHAR2(255 BYTE)          NOT NULL
);

INSERT INTO SC_DISTRICT VALUES(1,NULL,'四川省');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(2,1,'巴中市');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(3,1,'达州市'); 

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(4,2,'巴州区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(5,2,'通江县');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(6,2,'平昌县');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(7,3,'通川区');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(8,3,'宣汉县');

INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(9,8,'塔河乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(10,8,'三河乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(11,8,'胡家镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(12,8,'南坝镇');
 
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(13,6,'大寨乡');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(14,6,'响滩镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(15,6,'龙岗镇');
INSERT INTO SC_DISTRICT(ID,PARENT_ID,NAME) VALUES(16,6,'白衣镇');

COMMIT;

SELECT * FROM SC_DISTRICT;

1. 找出 巴中市(可以把巴中市 也一起 查询出来，也可以不查询巴中市本身那条数据) 的所有下属区域
SELECT  S1.ID,
        S1.PARENT_ID,
        S1.NAME,
        LEVEL ,
        SYS_CONNECT_BY_PATH(S1.NAME,'>')
FROM SC_DISTRICT S1 
        START WITH S1.ID = 2 ---表示从哪些数据开始找
CONNECT BY PRIOR S1.ID =  S1.PARENT_ID 

2. 找出 宣汉县 的所有上级（父级）区域
SELECT  S1.ID,
        S1.PARENT_ID,
        S1.NAME,
        LEVEL ,
        SYS_CONNECT_BY_PATH(S1.NAME,'>')
FROM SC_DISTRICT S1 
        START WITH S1.ID = 16 ---表示从哪些数据开始找
CONNECT BY PRIOR    S1.PARENT_ID = S1.ID

---工行
ID  PARENT_ID   名称
0    null       工行总行
1    0         浙江总行
2    1         西湖区分行
3    1         萧山区分行
4    1         临平区分行
5    4         东湖街道支行

客户号  开户机构
001      西湖区分行
002      东湖街道支行
003      浙江总行
004      临平区分行
 

-------递归的应用
1
2
3
4
5
6
7
8

---------------生成一组连续的数字
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <=8;

SELECT TO_DATE(20211220,'YYYYMMDD') AS BEGIN_DATE
        ,TO_DATE(20211227,'YYYYMMDD') AS END_DATE
FROM DUAL

2021/12/20
2021/12/21
2021/12/22
2021/12/23
2021/12/24
2021/12/25
2021/12/26
2021/12/27

SELECT LEVEL,T1.BEGIN_DATE
      ,LEVEL + T1.BEGIN_DATE -1 AS DDD
FROM (
      SELECT TO_DATE(20211220,'YYYYMMDD') AS BEGIN_DATE
              ,TO_DATE(20211227,'YYYYMMDD') AS END_DATE
      FROM DUAL
     ) T1 
CONNECT BY LEVEL <= T1.END_DATE - T1.BEGIN_DATE +1

----练习
CREATE TABLE TABLE_DATE (OP_DAY DATE);
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211201','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211202','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211203','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211204','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211205','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211208','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211209','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211210','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211213','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211214','YYYYMMDD'));
INSERT INTO TABLE_DATE VALUES ( TO_DATE('20211215','YYYYMMDD'));
COMMIT;

某图书馆 20211201 到 20211215 开放时间如下

开放日期：
20211201
20211202
20211203
20211204
20211205
20211208
20211209
20211210
20211213
20211214
20211215

根据上表，计算出 20211201 到 20211215  期间没有开放的那些日期

20211206
20211207
20211211
20211212

SELECT * FROM TABLE_DATE;

--思路  先生成一个 20211201 到 20211215 连续的日期，再和 TABLE_DATE表里的数据做比较 
--可以通过 MINUS 、  表关联 、 NOT IN  、 NOT EXISTS 这些方法做对比 
--方法一
SELECT * FROM 
(SELECT   
   LEVEL+TO_DATE('20211201','YYYYMMDD')-1  AS OP_DAY
FROM DUAL
CONNECT BY LEVEL <= 15)  T
WHERE NOT EXISTS (SELECT 1 FROM TABLE_DATE T1 WHERE T1.OP_DAY = T.OP_DAY); 
--方法二
SELECT * FROM 
(SELECT   
   LEVEL+TO_DATE('20211201','YYYYMMDD')-1  AS OP_DAY
FROM DUAL
CONNECT BY LEVEL <= 15)  T
WHERE T.OP_DAY NOT IN (SELECT OP_DAY FROM TABLE_DATE );

--方法三
SELECT Q.OP_DAY FROM 
(SELECT   
   LEVEL+TO_DATE('20211201','YYYYMMDD')-1 AS  OP_DAY
FROM DUAL
CONNECT BY LEVEL <= 15) Q
MINUS
SELECT T.OP_DAY  FROM TABLE_DATE T

SELECT E1.EMPNO,E1.ENAME,E1.MGR,E2.EMPNO,E2.ENAME FROM  EMP E1 INNER JOIN  EMP E2 ON   E1.MGR = E2.EMPNO;