27,579
社区成员
发帖
与我相关
我的任务
分享
-- 试试这个
/*
create table [ceshi](id int, parentid int, code nvarchar(50))
go
insert into [ceshi]
select 1,0,'a' union all
select 2,1,'b' union all
select 3,1,'c' union all
select 4,2,'d' union all
select 5,2,'e' union all
select 6,2,'f' union all
select 7,3,'g' union all
select 8,3,'h' union all
select 9,4,'i' union all
select 10,5,'j' union all
select 11,9,'k' union all
select 12,7,'l' union all
select 13,7,'m' union all
select 14,8,'n' union all
select 15,13,'o' union all
select 16,14,'p'
*/
go
-- 查找条件
declare @s varchar(100) = 'g'
-- 获取父节点和子节点的数据
;with ParentCode as(
select id,parentid,code from ceshi where code = @s
union all
select a.id,a.parentid,a.code from ceshi a join ParentCode d on a.id = d.parentid
),ChildCode as (
select id,parentid,code from ceshi where code = @s
union all
select a.id,a.parentid,a.code from ceshi a join ChildCode c on a.parentid = c.id
)
/*
--显示数据
select * from (
select * from ParentCode
union
select * from ChildCode
) t
*/
--合并字符串
select stuff((select ','+ code
from (
select * from ParentCode
union
select * from ChildCode
) t
for xml path('')) , 1,1,'')