laravel查询数据使用find_使用Select WITH 子句递归查询分层数据

本文介绍如何使用WITH子句查询带有层次关系的数据。此功能是Oracle11.2版本发布的。另一种用于分层查询的子句是CONNECT BY，后续的文章会有专门介绍。

实验环境： CentOS Linux release 7.6.1810 (Core) Oracle Database 18c Express

创建测试数据

--此测试数据可以直接在网上下载，搜索"全国省市区数据库sql版"即可。--这里为了演示我只选取辽宁的数据，其它省份的数据就忽略了。CREATE TABLE CITY(ID NUMBER PRIMARY KEY, PID NUMBER DEFAULT NULL, CITYNAME VARCHAR2(50) DEFAULT NULL, TYPE NUMBER DEFAULT NULL);  insert into CITY (ID, PID, CITYNAME, TYPE) values (18,1,'辽宁',1);insert into CITY (ID, PID, CITYNAME, TYPE) values (244,18,'沈阳',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (245,18,'大连',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (246,18,'鞍山',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (247,18,'本溪',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (248,18,'朝阳',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (249,18,'丹东',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (250,18,'抚顺',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (251,18,'阜新',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (252,18,'葫芦岛',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (253,18,'锦州',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (254,18,'辽阳',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (255,18,'盘锦',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (256,18,'铁岭',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (257,18,'营口',2);insert into CITY (ID, PID, CITYNAME, TYPE) values (2060,244,'沈河区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2061,244,'皇姑区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2062,244,'和平区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2063,244,'大东区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2064,244,'铁西区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2065,244,'苏家屯区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2066,244,'东陵区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2067,244,'沈北新区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2068,244,'于洪区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2069,244,'浑南新区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2070,244,'新民市',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2071,244,'辽中县',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2072,244,'康平县',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2073,244,'法库县',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2074,245,'西岗区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2075,245,'中山区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2076,245,'沙河口区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2077,245,'甘井子区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2078,245,'旅顺口区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2079,245,'金州区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2080,245,'开发区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2081,245,'瓦房店市',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2082,245,'普兰店市',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2083,245,'庄河市',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2084,245,'长海县',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2085,246,'铁东区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2086,246,'铁西区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2087,246,'立山区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2088,246,'千山区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2089,246,'岫岩',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2090,246,'海城市',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2091,246,'台安县',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2092,247,'本溪',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2093,247,'平山区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2094,247,'明山区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2095,247,'溪湖区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2096,247,'南芬区',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2097,247,'桓仁',3);insert into CITY (ID, PID, CITYNAME, TYPE) values (2098,248,'双塔区'