如何一次性显示出 某一层下所有子节点 返回表
create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered
,pid 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, '旧金山 '
union all select 4, '旧金山1 '
union all select 5, '旧金山2 '
go
select * from tb
如何一次性显示出 某一层下所有子节点
传入参数1 层 得到
id Sort
－－－－－－－－－－－－－－－－
1 4,5,6,7,8,9,10,13,14
2 11,12
3
传入参数2 层 得到
id Sort
－－－－－－－－－－－－－－－－
4 13
5 14
6 7,9,10
11
12
－－－－－问题2
或者返回 这样的语句 形式
传入参数1 层 得到
SELECT
id1=StateName=sum(
CASE id
WHEN 4 THEN 0
WHEN 5 THEN 0
WHEN 6 THEN 0
WHEN 7 THEN 1
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 1
WHEN 13 THEN 0
WHEN 14 THEN 0
END
,
id2=StateName=sum(
CASE id
WHEN 11 THEN 0
WHEN 12 THEN 0
END
)
FROM tb
[解决办法]
create table
tb
(
id int identity(1,1) not null constraint PK_tb primary key clustered
,pid 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, '旧金山 '
union all select 4, '旧金山1 '
union all select 5, '旧金山2 '
go
--如何一次性显示出 某一层下所有子节点
declare @t_level table (id int,id1 int ,pid int,col varchar(50))
insert into @t_level select id,id,pid,cast(pid as varchar) from tb where pid=0
while @@rowcount > 0
begin
update @t_level set pid=a.id,id1=a.id,col=col+ ', '+cast(a.id as varchar) from tb a inner join @t_level b on a.pid=b.id1
end
select * from @t_level
--
id id1 pid col
----------- ----------- ----------- ------------
1 13 13 0,4,13
2 11 11 0,11
3 3 0 0
[解决办法]
create table
tb
(
id int identity(1,1) not null constraint PK_tb primary key clustered
,pid 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, '旧金山 '
union all select 4, '旧金山1 '
union all select 5, '旧金山2 '
go
--如何一次性显示出 某一层下所有子节点
--drop function dbo.fn_split
create function dbo.fn_split(@id int)
returns varchar(50)
as
begin
declare @s varchar(20)
set @s= ' '
declare @tb table(id int,lv int)
declare @level int
set @level=0
select @s=@s+ ', '+cast(id as varchar) from tb where pid=@id
insert into @tb select id,@level from tb where pid=@id
while @@rowcount> 0
begin
set @level=@level+1
select @s=@s+ ', '+cast(a.id as varchar) from tb a inner join @tb b on a.pid=b.id and b.lv=@level-1
insert into @tb select a.id,@level from tb a inner join @tb b on a.pid=b.id and b.lv=@level-1
end
return stuff(@s,1,1, ' ')
end
go
--drop table tb
select id,dbo.fn_split(id) from tb where pid=0
id
----------- --------------------------------------------------
1 4,5,6,7,9,10,13,14,
2 11,12
3 NULL
