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;