SQL练习题二(按等级显示，递归钻取)

SQL练习题二(按等级显示，递归钻取)

---

有一张地区市的表TABLE2，如图

要得到如下结果：

---

一、表TABLE2的创建数据

CREATE TABLE TABLE2(
ID INT,
NAME VARCHAR(15),
PARENTID INT
);

INSERT INTO TABLE2 VALUES (1,'北京市',0);
INSERT INTO TABLE2 VALUES (2,'广东省',0);
INSERT INTO TABLE2 VALUES (3,'昌平区',1);
INSERT INTO TABLE2 VALUES (4,'海淀区',1);
INSERT INTO TABLE2 VALUES (5,'广州市',2);
INSERT INTO TABLE2 VALUES (6,'深圳市',2);
INSERT INTO TABLE2 VALUES (7,'沙河镇',3);
INSERT INTO TABLE2 VALUES (8,'中关村',4);
INSERT INTO TABLE2 VALUES (9,'天河区',5);
INSERT INTO TABLE2 VALUES (10,'福田区',6);

---

二、实现

1.通用

SELECT
a.NAME 一级地名
,b.NAME 二级地名
,c.NAME 三级地名
FROM TABLE2 a
JOIN TABLE2 b on a.ID=b.PARENTID
JOIN TABLE2 c on b.ID=c.PARENTID

---

2.用with as

WITH T1 AS
(
SELECT a.ID,a.NAME,a.PARENTID 
FROM TABLE2 a 
WHERE PARENTID=0
),
T2 AS
(
SELECT b.ID,b.NAME,b.PARENTID 
FROM TABLE2 b 
JOIN T1 ON b.PARENTID=T1.ID
),
T3 AS
(
SELECT c.ID,c.NAME,c.PARENTID 
FROM TABLE2 c 
JOIN T2 ON c.PARENTID=T2.ID
)

SELECT 
T1.NAME 一级地名
,T2.NAME 二级地名
,T3.NAME 三级地名
FROM T1
JOIN T2 ON T1.ID=T2.PARENTID
JOIN T3 ON T2.ID=T3.PARENTID

---

3.postgreSQL数据库

WITH RECURSIVE T1 AS
(
SELECT ID,NAME,PARENTID,1 AS LEVEL
FROM TABLE2 WHERE PARENTID=0
UNION ALL
SELECT t.ID,t.NAME,t.PARENTID,T1.LEVEL+1 AS LEVEL
FROM TABLE2 t
JOIN T1 ON t.PARENTID=T1.ID
)

SELECT
a.NAME 一级地名
,b.NAME 二级地名
,c.NAME 三级地名
FROM(SELECT * FROM T1 WHERE LEVEL=1) a
JOIN (SELECT * FROM T1 WHERE LEVEL=2) b ON a.ID=b.PARENTID
JOIN (SELECT * FROM T1 WHERE LEVEL=3) c ON b.ID=c.PARENTID

---

4.Oracle数据库

WITH T1 AS
(
 SELECT A.*, LEVEL AS CODE
FROM TABLE2 A 
START WITH A.PARENTID = 0
CONNECT BY PRIOR A.ID = A.PARENTID
ORDER BY CODE,A.ID
)
SELECT 
B.NAME 一级地名
,C.NAME 二级地名
,D.NAME 三级地名
FROM (SELECT * FROM T1 WHERE CODE = 1) B
JOIN (SELECT * FROM T1 WHERE CODE = 2) C ON C.PARENTID = B.ID
JOIN (SELECT * FROM T1 WHERE CODE = 3) D ON D.PARENTID = C.ID

---

三、总结：

遇到树形结构的数据时，oracle可以使用层次化查询connect by遍历表数据，而在postgreSQL数据库中，我们使用RECURSIVE参数配合with查询来实现遍历。还有其他方法的小伙伴欢迎来分享。