千里猪:你的思想是对的。但是你的“SELECT *
FROM t_function
where not exists(select * from t_function a where a.parent = id)
”
写错了。select * from t_function a where a.parent = id这个结果将是个空集。所以总的结果将是整个表。
select *
into #@linshibiao
from A1
where qianID = 'a'
insert jieguo
select * from A1
where qianID = 'a'
while exists(select a.qianID,b.houID,a.menber*b.menber as menber from #@linshibiao a join A1 b on (a.houID = b.qianID and a.qianID = 'A'))
begin
select a.qianID,b.houID,a.menber*b.menber as menber
into #@zhongjianbiao
from #@linshibiao a join A1 b
on (a.houID = b.qianID and a.qianID = 'A')
insert jieguo
select a.qianID,b.houID,a.menber*b.menber as menber
from #@linshibiao a join A1 b
on (a.houID = b.qianID and a.qianID = 'A')
truncate table #@linshibiao
insert into #@linshibiao
select * from #@zhongjianbiao
drop table #@zhongjianbiao
create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered
,parentID int,name varchar(20))
insert into tb
select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go
declare @i int
set @i=1
create table #tmp(id int,px varchar(1000),parentid int)
insert into #tmp select [id], '', parentID from tb order by id
update #tmp set px=cast(@i+10000000 as varchar) ,@i=@i+1
from #tmp where parentid=0
while @@rowcount>0
update b set b.px=a.px+cast(@i+10000000 as varchar),@i=@i+1
from #tmp a join #tmp b
on a.id=b.parentID and a.px<>'' and b.px=''
select b.*,space(len(a.px)/2)+b.name 缩进的 from
#tmp a join tb b on a.id=b.id order by a.px
drop table #tmp
drop table tb