/*递归3层查询
         * 表LKS_T
         * 字段有MID、PID、Mtxt
         * 先查出Mtxt='文件'的MID和Mtxt 重命名为MID1和Mtxt1
         * 根据查来的MID1，查询MID和Mtxt，条件PID=MID1 重命名为MID2和Mtxt2
         * 根据查来的MID2，查询MID和Mtxt，条件PID=MID2 重命名为MID3和Mtxt3
         * 要得到的结果：MID1 txt1 MID2 txt2 MID3 txt3
         * 结束
         */

IF NOT OBJECT_ID('tb') IS NULL 
    DROP TABLE tb
create table tb (mid varchar(50), pid varchar(50),mtxt varchar(50))
insert into tb select 'a1','a2','a'
insert into tb select 'a2','a3','b'
insert into tb select 'a3','a4','c'

with at as
(select a.mid,a.pid,a.mtxt ,1 as levl from tb a where  a.mtxt='a'---- a1为参数
union all
select a.mid,a.pid,a.mtxt,levl + 1 from tb a join at on  a.mid=at.pid
)
select max(case when levl=1 then mid else null end )'mid1',max(case when levl=1 then mtxt else null end) 'txt1' 
,max(case when levl=2 then mid else null end) 'mid2',max(case when levl=2 then mtxt else null end )'txt2'  
,max(case when levl=3 then mid else null end) 'mid3',max(case when levl=3 then mtxt else null end )'txt3'
from at
/*
mid1    txt1    mid2    txt2    mid3    txt3
a1    a    a2    b    a3    c

------解决方案--------------------
SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([MID] int,[PID] int,[Mtxt] varchar(5))
insert [test]
select 2,0,'测试1' union all
select 10,2,'测试2' union all
select 11,10,'测试3'

select t.*,m.MID as mid3,m.Mtxt as mtxt3 from (
select a.MID as mid1,a.Mtxt Mtxt1,b.MID mid2,b.Mtxt Mtxt2  from test a
inner join test b on a.MID=b.PID)t
inner join test m on t.mid2=m.PID
/*
mid1    Mtxt1    mid2    Mtxt2    mid3    mtxt3
2    测试1    10    测试2    11    测试3
*/

三层递归没有必要用cte