递归查询分类树

-- 递归查询分类树
WITH CategoryTree AS (
    -- 基础查询：获取所有一级分类（Code长度为1和2）
    SELECT 
        ID,
        Code,
        Code AS FullCode, -- 父级编码(用于递归子级使用)
        1 AS Level, -- 层级
        CAST(Code AS VARCHAR(1000)) AS Path -- 分类层级平铺
    FROM CBO_Category
    WHERE
        Org = 1002111230110267 -- 组织ID
        AND LEN(Code) in(1,2)
    
    UNION ALL
    
    -- 递归查询：获取子分类
    SELECT 
        t.ID,
        t.Code,
        t.Code AS FullCode,
        ct.Level + 1 AS Level,
        CAST(CONCAT(ct.Path, '_', t.Code) AS VARCHAR(1000)) AS Path  -- 使用相同的类型和长度
    FROM CBO_Category AS t
    JOIN CategoryTree AS ct ON 
        LEN(t.Code) = LEN(ct.FullCode) + 2 AND 
        t.Code LIKE CONCAT(ct.FullCode, '%')
    WHERE
        Org = 1002111230110267 -- 组织ID
        AND t.Code <> ct.FullCode
)
SELECT 
    ID,
    Code,
    Level,
    Path
FROM CategoryTree
ORDER BY Path;