树数据集

-- leveal列最大值是多少，就写多少个join，如果想自动化，就需要存储循环或游标

select lev0.typeID typeID0,lev1.typeID typeID1,lev2.typeID typeID2,

       lev0.fullName fullName0,lev1.fullName fullName1,lev2.fullName fullName2

from (

select typeID,parID,fullName from tableName 

where 1=1

  and leveal=0

) lev0

left join (

select typeID,parID,fullName from tableName 

where 1=1

  and leveal=1

) lev1 on lev0.typeID=lev1.parID

left join (

select typeID,parID,fullName from tableName 

where 1=1

  and leveal=2

) lev2 on lev1.typeID=lev2.parID

WITH TreeCTE AS (

    -- 定位点成员：根节点

    SELECT 

        typeid, 

        parid, 

        level,

        CAST(typeid AS VARCHAR(MAX)) AS path,  -- 路径跟踪

        0 AS is_leaf,  -- 是否为叶节点

        CAST(typeid AS VARCHAR(MAX)) AS sort_path  -- 排序路径

    FROM TreeTable

    WHERE parid = 'root'  -- 根节点条件

    UNION ALL

    -- 递归成员：子节点

    SELECT 

        t.typeid, 

        t.parid, 

        t.level,

        c.path + ' > ' + t.typeid,  -- 构建路径

        CASE WHEN NOT EXISTS (SELECT 1 FROM TreeTable WHERE parid = t.typeid) 

             THEN 1 ELSE 0 END,  -- 判断叶节点

        c.sort_path + '_' + t.typeid  -- 层级排序

    FROM TreeTable t

    INNER JOIN TreeCTE c ON t.parid = c.typeid

)

SELECT 

    typeid AS [节点ID],

    parid AS [父ID],

    level AS [层级],

    path AS [节点路径],

    CASE is_leaf 

        WHEN 1 THEN '叶节点' 

        ELSE '分支节点' 

    END AS [节点类型]

FROM TreeCTE

ORDER BY sort_path;  -- 按层级排序