一个SQL递归查询 实在不会递归 多谢

        /*递归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 tbcreate 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 allselect 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    txt3a1    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 allselect 10,2,'测试2' union allselect 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 ainner join test b on a.MID=b.PID)tinner join test m on t.mid2=m.PID/*mid1    Mtxt1    mid2    Mtxt2    mid3    mtxt32    测试1    10    测试2    11    测试3*/三层递归没有必要用cte