表结构如下
create table t00370(lefid varchar(10),rotid varchar(20),no int)
insert tree select '1211 ', '1200 ',1
union all select '1258 ', '1200 ',2
union all select '1235 ', '1200 ',3
union all select '1015 ', '1258 ',1
union all select '1234 ', '1258 ',2
union all select '3254 ', '1234 ',1
union all select '1250 ', '1200 ',4
union all select '1582 ', '1258 ',3
union all select '1532 ', '1258 ',4
union all select '5623 ', '1532 ',1
union all select '1564 ', '1532 ',2
有以上一个表的结构,想用SQL语句查出一个树型结构,结果如下
1200
--1211 1
--1258 2
--1015 1
--1234 2
--1582 3
--1532 4
--5623 1
--1564 2
--1235 3
--1250 4
请求哪位高人能帮我写出代码,谢谢了!!!
------解决方案--------------------
create table tree(id varchar(10),parentid varchar(20),no int)
insert tree select '1211 ', '1200 ',1
union all select '1258 ', '1200 ',2
union all select '1235 ', '1200 ',3
union all select '1015 ', '1258 ',1
union all select '1234 ', '1258 ',2
union all select '3254 ', '1234 ',1
union all select '1250 ', '1200 ',4
union all select '1582 ', '1258 ',3
union all select '1532 ', '1258 ',4
union all select '5623 ', '1532 ',1
union all select '1564 ', '1532 ',2
union all select '1200 ', '0 ',0 ---新加的用于表示根节点（parentid= '0 ')
go
create proc p1
as
create table #temp(id varchar(10),Name varchar(4000),ParentID varchar(10),
treecode varchar(1000),level int,no int)
declare @level int
select @level=0
insert into #temp select id, '|__ ' +id,parentid, '0 ',0,no
from tree a where parentid= '0 '
while @@rowcount> 0
begin
set @[email protected]+1
insert into #temp select a.id,replace(case when exists(select 1 from #temp where parentid=b.parentid and no> b.no )
then substring(b.name,1,@level*3) else substring(b.name,1,@level*3-3)+ ' ' end, '__ ', ' ')
+ '|__ ' +a.id,
a.parentid,b.treecode+ '.( '+cast(a.no as varchar)+ ') '+convert(varchar,a.id),@level,a.no
from tree a inner join #temp b on
a.parentid=b.id and [email protected]