树
来源:互联网 发布:淘宝售后统计表 编辑:程序博客网 时间:2024/04/28 13:40
create table 部门表(depid varchar(20),name varchar(20),depid_up varchar(20))
insert into 部门表 select '001','部门一','003'
union all select '002','部门二','003'
union all select '003','大部门一',''
union all select '004','部门三','003'
union all select '005','大部门二',''
union all select '006','大部门三',''
union all select '007','部门四','005'
union all select '008','部门五','005'
select depid,name from
(
select depid , name, px=1 from 部门表 where isnull(depid_up,'')=''
union all
select a.depid,'--'+b.name+'--', px=2 from 部门表 a,部门表 b where a.depid=b.depid_up) torder by depid, px
insert into 部门表 select '001','部门一','003'
union all select '002','部门二','003'
union all select '003','大部门一',''
union all select '004','部门三','003'
union all select '005','大部门二',''
union all select '006','大部门三',''
union all select '007','部门四','005'
union all select '008','部门五','005'
select depid,name from
(
select depid , name, px=1 from 部门表 where isnull(depid_up,'')=''
union all
select a.depid,'--'+b.name+'--', px=2 from 部门表 a,部门表 b where a.depid=b.depid_up) torder by depid, px