--用函数解决吧! --参考一下: 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-- 查询示例 select dbo.f_nodes(1) '1的下级' select dbo.f_nodes(2) '2的下级' select dbo.f_nodes(3) '3的下级'--删除测试环境 drop function f_nodes drop table A
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,9select * from #tbselect * from (select uid1 from #tb)a , (select uid2 from #tb)buid1 uid2 --------------- 1 2 2 2 3 2 3 2 7 2 8 2 1 3 2 3 3 3 3 3 7 3 8 3 1 4 2 4 3 4 3 4 7 4 8 4 1 5 2 5 3 5 3 5 7 5 8 5 1 1 2 1 3 1 3 1 7 1 8 1 1 9 2 9 3 9 3 9 7 9 8 9
這種就是交叉連接查詢的,也可以用這樣的語句select * from (select uid1 from #tb)a cross join (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-- 查询示例
select dbo.f_nodes(1) '1的下级'
select dbo.f_nodes(2) '2的下级'
select dbo.f_nodes(3) '3的下级'--删除测试环境
drop function f_nodes
drop table A
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,9select * from #tbselect *
from (select uid1 from #tb)a , (select uid2 from #tb)buid1 uid2
---------------
1 2
2 2
3 2
3 2
7 2
8 2
1 3
2 3
3 3
3 3
7 3
8 3
1 4
2 4
3 4
3 4
7 4
8 4
1 5
2 5
3 5
3 5
7 5
8 5
1 1
2 1
3 1
3 1
7 1
8 1
1 9
2 9
3 9
3 9
7 9
8 9
from (select uid1 from #tb)a
cross join (select uid2 from #tb)b