SQL遍历父子关系表（二叉树）获得所有子节点
工具/原料
sql管理工具
计算机
方法/步骤
1、先建立需要测试的表格，及插入测试数据Create哌囿亡噱TableA(IDInt,fatherIDInt,NameVarchar(10))InsertASelect1,惺绅寨瞀NULL,'tt'UnionAllSelect2,1,'aa'UnionAllSelect3,1,'bb'UnionAllSelect4,2,'cc'UnionAllSelect5,2,'gg'UnionAllSelect6,4,'yy'UnionAllSelect7,4,'jj'UnionAllSelect8,7,'ll'UnionAllSelect9,NULL,'uu'UnionAllSelect10,9,'oo'GO
2、执行该语句，得到相应的数据库表格和数据
3、创建相应的遍历函数CreateFunctionGetChildren(@IDInt拘七呷憎)Returns@TreeTable(IDInt,fatherIDInt,Na罪焐芡拂meVarchar(10))AsBeginInsert@TreeSelectID,fatherID,NameFromAWherefatherID=@IDWhile@@Rowcount>0Insert@TreeSelectA.ID,A.fatherID,A.NameFromAAInnerJoin@TreeBOnA.fatherID=B.IDAndA.IDNotIn(SelectIDFrom@Tree)ReturnEndGO
4、现在进行测试下函数的执行效果Select*Fromdbo.GetChildren(1)GO
5、删除测试DropTableADropFunctionGetChildren--結果/*IDfatherIDName21aa31bb42cc52gg64yy74jj87ll