`
收藏列表
标题 标签 来源
oracle sql 查询树结构并且内部还有排序 oracle sql 查询树结构并且内部还有排序 
create table TEST
(
  NAME     VARCHAR2(100),
  PARENTID INTEGER,
  ID       INTEGER,
  SORT     INTEGER
);

insert into test (NAME, PARENTID, ID, SORT)
values ('A', 0, 1, 1);

insert into test (NAME, PARENTID, ID, SORT)
values ('AA', 1, 4, 1);

insert into test (NAME, PARENTID, ID, SORT)
values ('AAA', 4, 8, 1);

insert into test (NAME, PARENTID, ID, SORT)
values ('AAC', 4, 10, 3);

insert into test (NAME, PARENTID, ID, SORT)
values ('AAB', 4, 9, 4);

insert into test (NAME, PARENTID, ID, SORT)
values ('C', 0, 3, 2);

insert into test (NAME, PARENTID, ID, SORT)
values ('CC', 3, 5, 2);

insert into test (NAME, PARENTID, ID, SORT)
values ('B', 0, 2, 3);

insert into test (NAME, PARENTID, ID, SORT)
values ('BB', 2, 6, 3);

insert into test (NAME, PARENTID, ID, SORT)
values ('BBB', 6, 7, 2);
select * from  test d start with d.parentid=-1 connect by prior d.id=d.parentid  ORDER SIBLINGS BY d.sort;

--start with 从那个父节点开始
--connect by prior 子与父关联 ，注意：一定要子在前面等于父在后面（d.id=d.parentid）
--ORDER SIBLINGS BY 还需要按照那个字段排序
Global site tag (gtag.js) - Google Analytics