SQL 递归

最新推荐文章于 2025-12-05 20:44:21 发布

原创最新推荐文章于 2025-12-05 20:44:21 发布 · 238 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #java

博客围绕SQL数据库展开，包含基本查询语句，如从EMP表查询员工信息。介绍树状递归查询，可往子项和父项查找。还给出创建表、插入数据操作，以及递归在查找区域上下级、生成连续数字和日期方面的应用，最后探讨计算图书馆未开放日期的多种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;