紧急求教高人,关于树型结构查询语句,
表结构如下
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 @level=@level+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 b.level=@level-1
end
select * from #temp order by treecode
drop table #temp
go
p1
drop table tree
drop proc p1
[解决办法]
DECLARE @t TABLE(id varchar(10),parentid varchar(20),no int)
insert @t 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
DECLARE @t_Level TABLE(ID varchar(10),Level int,Sort varchar(8000),Sort1 varchar(10))
DECLARE @Level int
SET @Level=0
INSERT @t_Level
SELECT id,@Level,id,null
FROM @t
WHERE [no]=0
WHILE @@ROWCOUNT> 0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID,cast(a.[no] as varchar(10))
FROM @t a,@t_Level b
WHERE a.parentid=b.ID
AND b.Level=@Level-1
END
SELECT c1=case when Sort1 is null then SPACE(b.Level*4)+a.id else SPACE(b.Level*4)+ '-- '+a.id end ,
c2=isnull(b.Sort1, ' ')
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort,b.Sort1
