sql树形结构

create temporary table `treeView`(
  `id` varchar(20),
  `org_name` varchar(20),
  `parent_org_id` varchar(20),
  `org_code` varchar(20)
)
insert into treeView(id,org_name,parent_org_id,org_code)values('1','根',null,'1');
insert into treeView(id,org_name,parent_org_id,org_code)values('2','机构1','1','2');
insert into treeView(id,org_name,parent_org_id,org_code)values('3','机构1—1','2','2');
insert into treeView(id,org_name,parent_org_id,org_code)values('4','机构2','1','1');
insert into treeView(id,org_name,parent_org_id,org_code)values('5','机构2-1','4','1');
insert into treeView(id,org_name,parent_org_id,org_code)values('6','机构1-2','2','1');
create temporary table `tree`(
    select id,org_name,parent_org_id,org_code from treeView where parent_org_id in (null,'1')
)
insert into tree(id,org_name,parent_org_id,org_code)
select id,org_name,parent_org_id,org_code from tree
UNION All
select t.id,t.org_name,t.parent_org_id,t.org_code from treeView as t,tree as v
    where t.parent_org_id = v.id order by t.org_code
    DROP TABLE treeView
    DROP TABLE tree
select * from tree

 

转载于:https://www.cnblogs.com/wqff-biubiu/p/9082779.html