Oracle树形结构数据CONNECT BY PRIOR查询及Ztree前端页面数据展示
每本书基本都有目录，打开word也会出现目录。如树枝一样，目录下还有子目录和同级目录。如图所示
创建表：
CREATE TABLE Z_TEEE(
ID NUMBER(8) NOT NULL,
P_ID NUMBER(8) NOT NULL,
NAME VARCHAR2(32) NOT NULL,
DEPTH NUMBER(8) NOT NULL,
RANK NUMBER(8) NOT NULL,
PRIMARY KEY (ID)
);
COMMENT ON TABLE Z_TEEE IS '表面';
COMMENT ON COLUMN Z_TEEE.ID IS '表ID';
COMMENT ON COLUMN Z_TEEE.P_ID IS '父级ID';
COMMENT ON COLUMN Z_TEEE.NAME IS 目录名称';
COMMENT ON COLUMN Z_TEEE.DEPTH IS '菜单层级';
COMMENT ON COLUMN Z_TEEE.RANK IS '菜单排序';
注意：CONNECT BY PRIOR主要依据ID与pid即目录ID与父级id进行查询，而前端ztree依靠id与pId进行识别，可以在setting 中进行设置。
插入数据：
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (2,1,'目录1',1,3);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (3,1,'目录2',1,2);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (4,1,'目录3',1,1);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (5,2,'目录1.1',2,1);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (6,2,'目录1.2',2,2);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (7,2,'目录1.3',2,3);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (8,5,'目录1.1.1',3,1);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (9,5,'目录1.1.2',3,2);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (10,4,'目录3.1',2,1);
insert into Z_TEEE (ID, P_ID, NAME, DEPTH, RANK)values (11,4,'目录3.2',2,2);
查询结果： SELECT * FROM Z_TREE ;
树状查询：
SELECT * FROM Z_TEEE START WITH ID IN (SELECT ID FROM Z_TEEE WHERE p_id = 1)CONNECT BY PRIOR ID = p_id ORDER SIBLINGS BY RANK ;
解释：p_id=1查询子目录为1的节点，在插入数据id是从2开始，使用pid为1，就可查询全部列表。此处是rank进行排序的结果，可以记录目录排序。depth字段为预留字段，可以去掉。同事很郁闷的是MySQL不支持 CONNECT BY PRIOR方法。
查询返回字符串如下：
[{"depth":1,"id":4,"name":"目录3","pId":1,"rank":1},
{"depth":2,"id":10,"name":"目录3.1","pId":4,"rank":1},
{"depth":2,"id":11,"name":"目录3.2","pId":4,"rank":2},
{"depth":1,"id":3,"name":"目录2","pId":1,"rank":2},
{"depth":1,"id":2,"name":"目录1","pId":1,"rank":3},
{"depth":2,"id":5,"name":"目录1.1","pId":2,"rank":1},
{"depth":3,"id":8,"name":"目录1.1.1","pId":5,"rank":1},
{"depth":3,"id":9,"name":"目录1.1.2","pId":5,"rank":2},
{"depth":2,"id":6,"name":"目录1.2","pId":2,"rank":2},
{"depth":2,"id":7,"name":"目录1.3","pId":2,"rank":3}]
该结构可以使用ztree简单模式即可如图1所示，tree可以查看ztree官网demo演示加载
ajax请求
var setting = {
data: {
simpleData: {
enable: true,//是否可编辑
idKey: "id",//当前目录id
pIdKey: "pId"//当前目录父类id，可以根据自己需求修改该值
},
key: {
name: "name"//目录名称，在前台显示字段
}
}
};
$(document).ready(function(){
var zNodes;
$.ajax({
type:"post",
url:"接口url",
data: { id: "1" },
dataType: "text",
ContentType: "application/json; charset=utf-8",
success: function (data) {
zNodes = data;
$.fn.zTree.init($("#ztree"), setting, eval('(' + zNodes + ')'));
},
error: function (msg) {
alert("失败");
}
});
});
即可将ID为ztree的div用ztree显示。
