经典树型表结构
create table t_tree
(
id number(8) not null,
pid number(8) default 0 not null,--上级节点,0表示1一级根节点
sort_no number(8),--树排序号
id_level number(2),--树层级
sub_sort number(4),--同级排序号
constraint pk_t_tree primary key (id)
);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (1, 0, 1, 1, 5);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (11, 1, 2, 2, 5);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (111, 11, 3, 3, 5);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (112, 11, 4, 3, 10);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (113, 11, 5, 3, 15);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (12, 1, 6, 2, 10);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (13, 1, 7, 2, 15);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (131, 13, 8, 3, 5);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (132, 13, 9, 3, 10);
insert into t_tree (ID, PID, SORT_NO, ID_LEVEL, SUB_SORT)values (133, 13, 10, 3, 15);
全显示三级,根据sort_no整棵树排序
select * from t_tree order by sort_no;
仅显示两级,根据id_level<=2查询
select * from t_tree where id_level<=2 order by sort_no;
id_level等用于connect by语法中的level
select t.*, level from t_tree t connect by prior id = pid start with id = 1
order by sort_no;
在java中根据sub_sort排序+pid即可构造出JTree对象
select * from t_tree order by sub_sort;