SQL Server 递归查询,通过子id查询所有父级
1.表结构
2.SQL
--由子级递归父项
with cte(materialshelf_id,materialshelf_pid,materialshelf_name)
as
(--下级父项
select materialshelf_id,materialshelf_pid,materialshelf_name from materialshelf where materialshelf_id = 5 --子级id
union all
--递归结果集中的父项
select t.materialshelf_id,t.materialshelf_pid,t.materialshelf_name from materialshelf as t
inner join cte as c on t.materialshelf_id = c.materialshelf_pid
)
select materialshelf_id,materialshelf_pid,materialshelf_name from cte order by materialshelf_id asc
3.结果
4.SQL Server多行数据合并一个
--由子级递归父项
with cte(materialshelf_id,materialshelf_pid,materialshelf_name)
as
(--下级父项
select materialshelf_id,materialshelf_pid,materialshelf_name from materialshelf where materialshelf_id = 5 --子级id
union all
--递归结果集中的父项
select t.materialshelf_id,t.materialshelf_pid,t.materialshelf_name from materialshelf as t
inner join cte as c on t.materialshelf_id = c.materialshelf_pid
)
select stuff((
select materialshelf_name + '/'
from cte order by materialshelf_id asc for xml path('')),1,0,'') as materialshelf_name;
5.合并结果