?
目录树构建及查询案例(二)(二)
;
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200220', '东石窖村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200221', '西石窖村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200222', '碾底村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200223', '水峪村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200224', '东圪台头村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200225', '寺家坪村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200226', '武家崖村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200227', '程家沟村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200228', '陈家坪村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200229', '麦地掌村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200231', '西圪台头村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121200232', '张家山村委会', '140121200');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121201', '柳杜乡', '140121');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121202', '西谷乡', '140121');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121203', '王答乡', '140121');
insert into TREE (TREEID, LABLE, PARENTID) values ('140121204', '集义乡', '140121');
insert into TREE (TREEID, LABLE, PARENTID) values ('140122', '阳曲县', '140100');
insert into TREE (TREEID, LABLE, PARENTID) values ('140123', '娄烦县', '140100');
insert into TREE (TREEID, LABLE, PARENTID) values ('140181', '古交市', '140100');
insert into TREE (TREEID, LABLE, PARENTID) values ('140200', '大同市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140300', '阳泉市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140400', '长治市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140500', '晋城市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140600', '朔州市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140700', '晋中市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140800', '运城市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('140900', '忻州市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('141000', '临汾市', '140000');
insert into TREE (TREEID, LABLE, PARENTID) values ('141100', '吕梁市', '140000');
commit;
--山西省下属市
select * from tree
where treeid like '____00'
CONNECT BY PRIOR treeid = parentid
START WITH treeid = '140000'
ORDER BY treeid;
--太原市下属区县
select * from tree
where treeid like '1401__'
CONNECT BY PRIOR treeid = parentid
START WITH treeid = '140100'
ORDER BY treeid;
--清徐县下属乡镇及村
select * from tree
CONNECT BY PRIOR treeid = parentid
START WITH treeid = '140121'
ORDER BY treeid;
--马峪乡下属村
select * from tree
CONNECT BY PRIOR treeid = parentid
START WITH treeid = '140121200'
ORDER BY treeid;
--山西省所有的村
select * from tree
where lengthb(treeid)>
=12
CONNECT BY PRIOR treeid = parentid
START WITH treeid = '140000'
ORDER BY treeid;