ORACLE树形表结构查询 START WITH CONNECT BY PRIOR

最新推荐文章于 2023-10-27 20:20:18 发布

陈陈陈v

最新推荐文章于 2023-10-27 20:20:18 发布

阅读量488

点赞数

分类专栏： SQL 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u014064071/article/details/127554726

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

- ORACLE树形表结构查询 START WITH CONNECT BY PRIOR

-- 1 建表
CREATE TABLE tmp ( code varchar(255), name varchar(255), parent_code varchar(255) );
-- 2插入数据
INSERT INTO tmp VALUES ('01', '河南', NULL);
INSERT INTO tmp VALUES ('0101', '郑州', '01');
INSERT INTO tmp VALUES ('0102', '开封', '01');
INSERT INTO tmp VALUES ('0103', '洛阳', '01');
INSERT INTO tmp VALUES ('02', '上海', NULL);
INSERT INTO tmp VALUES ('0201', '徐汇区', '02');
INSERT INTO tmp VALUES ('0202', '嘉定区', '02');
INSERT INTO tmp VALUES ('010101', '中原区', '0101');
INSERT INTO tmp VALUES ('010102', '管城区', '0101');
INSERT INTO tmp VALUES ('020201', '安亭镇', '0202');
INSERT INTO tmp VALUES ('020202', '嘉定镇', '0202');
INSERT INTO tmp VALUES ('02020101', '黄渡镇', '020201');
-- 3遍历树状数据
-- 向下
SELECT * FROM tmp START WITH code = '0101' CONNECT BY PRIOR code = parent_code ;
-- 向上
SELECT * FROM tmp START WITH code = '0101' CONNECT BY code = PRIOR parent_code ;