create table #tb(uid1 int,uid2 int)
Insert into #tb
select 1,2
union all select 2,3
union all select 3,4
union all select 3,5
union all select 7,1
union all select 8,9
select * from #tb
select *
from (select uid1 from #tb)a , (select uid2 from #tb)b
--用函数解决吧!
--参考一下:
create table A
(
ID int,
[姓名] varchar(10),
[上级ID] int
)
insert A
select 1,'A',0 union
select 2,'B',1 union
select 3,'C',1 union
select 4,'D',2 union
select 5,'E',3 union
select 6,'F',4 union
select 7,'G',5
go
--创建函数
create function f_nodes(@ID int)
returns varchar(8000)
as
begin
declare @tb table(ID int,[上级ID] int)
insert @tb
select ID,[上级ID] from A where ID=@ID
while @@rowcount>0
begin
insert @tb
select A.ID
,A.[上级ID]
from A
join @tb B on A.[上级ID]=B.ID
where A.ID not in(select ID from @tb)
end
declare @str varchar(8000)
set @str=''
select @str=@str+','+convert(varchar,[ID]) from @tb
return stuff(@str,1,1,'')
end
go