根据子节点查找所有父节点

-- 方法一：通过父节点关联(递归)
with test(fid, fname, fparentid) as (
values('01', 'test01', '0')
union
values('0101', 'test0101', '01')
union
values('010101', 'test010101', '0101')
union
values('0102', 'test0102', '01')
union
values('02', 'test02', '0')
),
temp(fid, fname, fparentid) as (
select fid, fname, fparentid from test where fname = 'test0101'
union all
select parent.fid, parent.fname, parent.fparentid from temp as child, test as parent where child.fparentid = parent.fid
)
select distinct fid, fname, fparentid from temp


-- 方法2：通过fpath关联
with test(fid, fname, fparentid, fpath) as (
values('01', 'test01', '0', '0,01')
union
values('0101', 'test0101', '01', '0,0101')
union
values('010101', 'test010101', '0101', '0,0101,010101')
union
values('0102', 'test0102', '01', '0,0102')
union
values('02', 'test02', '0', '0,02')
)
-- 根据条件获取父
select parent.fid, parent.fname, parent.fpath
from test as parent, ( select fid, fpath from test where fname = 'test0101' ) as child
where left( child.fpath, length(parent.fpath) ) = parent.fpath
-- union会去掉相同的记录,union all就会有重复的记录
union
-- 根据条件获取子
select child.fid, child.fname, child.fpath
from test as child, ( select fid, fpath from test where fname = 'test0101' ) as parent
where left(child.fpath,length(parent.fpath)) = parent.fpath