目录

1.递归与LEVEL简介 

2.往子项找

3.往父项找

4.练习题 

练习1：找出BLAKE的直系下属有哪些？ 

练习2

5.递归的其他应用——生成连续的数字或日期

---

1.递归与LEVEL简介 

递归：从顶层到下一层级，一层一层递归去找。

递归里面有一个很重要的关键字，LEVEL——伪列（关键字），代表树形结构中的层级编号。

递归查询应用场景：组织架构图、文件路径分析、菜单导航树、账单父子关联、社交网络关系链等。 

递归查询语法：

SELECT level,a.* 
FROM 表名 a
--WHERE 条件1
START WITH  条件2 --设置起点，用来限制第一层的数据，或者叫根节点数据
CONNECT BY PRIOR 条件3 --用来指明在查找数据时以怎样的一种关系去查找

prior：--英文释义：在前的，优先的

表示上一层级的标识符。经常用来对下一层级的数据进行限制，但是不可以接伪列。

PRIOR放在子节点方向时，表示从上往下遍历，即从根节点向叶节点方向遍历。

sys_connect_by_path(字段，拼接符) ：将递归过程中的列进行拼接。  

2.往子项找

PRIOR 侧的字段是 EMPNO，就是往下属寻找

示例：找KING这个员工的所有下属

select level, EMP.*           -- 5
from EMP                      -- 1
where ENAME!='KING'           -- 4
START WITH ENAME = 'KING'     -- 2
connect by prior EMPNO = MGR  -- 3  设置起点，用来限制第一层的数据，或者叫根节点数据
order by LEVEL;               -- 6

 

3.往父项找

示例：查找7876员工的所有领导

select LEVEL, EMP.*
from EMP
where LEVEL != 1
start with EMPNO = 7876
connect by prior MGR = EMPNO;

 

4.练习题 

练习1：找出BLAKE的直系下属有哪些？ 

select LEVEL, sys_connect_by_path(ENAME, '-') subordinate
from EMP
where LEVEL = 2
start with ENAME = 'BLAKE'
connect by prior EMPNO = MGR;

 

练习2

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 LEVEL, s.*
from SC_DISTRICT s
-- where level >= 2
start with NAME = '巴中市'
connect by prior ID = PARENT_ID;

2. 找出 宣汉县 的所有上级(父级)区域

select LEVEL, s.*
from SC_DISTRICT s
where LEVEL != 1
start with NAME = '宣汉县'
connect by prior PARENT_ID = ID;

5.递归的其他应用——生成连续的数字或日期

面试题：写sql输出1~100

select LEVEL
from DUAL
connect by LEVEL <= 100;

示例 ：输出最近10天的日期

select trunc(sysdate) - LEVEL + 1 recent
from DUAL
connect by LEVEL <= 10
order by recent;

 

练习1：输出20~30

-- 方法一
select LEVEL
from DUAL
where LEVEL >= 20
connect by LEVEL <= 30;-- 方法二
select LEVEL + 19
from DUAL
connect by LEVEL <= 11;

练习2： 输出从2025-01-01到当前的所有日期

select to_date('2025-01-01', 'YYYY-MM-DD') + LEVEL - 1 now-- -1是为了显示起始日期
from DUAL
connect by LEVEL <= trunc(sysdate) - to_date('2025-01-01', 'YYYY-MM-DD') + 1;
-- 直接相减得到的是间隔天数，总天数 = 间隔天数 + 1-- 方法二：不使用递归，使用ROWNUM
SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') + ROWNUM - 1 AS date_value
FROM ALL_OBJECTS
WHERE ROWNUM <= TRUNC(SYSDATE) - TO_DATE('2025-01-01', 'YYYY-MM-DD') + 1
ORDER BY date_value;

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;SELECT * FROM TABLE_DATE;

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

开放日期：

20211201

20211202

20211203

20211204

20211205

20211208

20211209

20211210

20211213

20211214

20211215

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

select to_date('20211201', 'YYYYMMDD') + LEVEL - 1 day
from DUAL
connect by LEVEL <= to_date('20211215', 'YYYYMMDD') - to_date('20211201', 'YYYYMMDD') + 1
minus
select OP_DAY
from TABLE_DATE;-- 方法二：临时表
with t1 as (select TO_DATE('20211201', 'YYYYMMDD') + LEVEL - 1 dayfrom DUALconnect by LEVEL <= TO_DATE('20211215', 'YYYYMMDD') - TO_DATE('20211201', 'YYYYMMDD') + 1)
select day
from t1
where not exists(select OP_DAYfrom TABLE_DATEwhere OP_DAY = day);