【泛微E9】查询部门的部门层级以及所有上级部门

效果图如下：

field1：一级部门
field2：二级部门
field3：三级部门
field4：四级部门
field5：五级部门
field6：六级部门

创建视图，view_bmcjpath
视图定义如下：

WITH RECURSIVE department_tree (id, DEPARTMENTMARK, supdepid, depth, path) AS (  
    -- 初始化查询(非递归部分)  
    SELECT     
        id,     
        DEPARTMENTMARK,     
        supdepid,    
        1 AS depth,    
        CAST(id AS CHAR(200)) AS path    
    FROM hrmdepartment    
    WHERE canceled IS NULL OR canceled <> 1  
    UNION ALL  
    -- 递归查询部分  
    SELECT     
        D.id,    
        D.DEPARTMENTMARK,     
        D.supdepid,    
        dt.depth + 1 AS depth,     
        CONCAT(dt.path, ',', D.id) AS path    
    FROM hrmdepartment D     
    JOIN department_tree dt ON D.supdepid = dt.id    
    WHERE D.canceled IS NULL OR D.canceled <> 1  
),  
ranked_departments AS (  
    SELECT     
        *,    
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY depth DESC) AS rn    
    FROM department_tree    
)  
-- 选择每个id对应depth最大的记录，并应用字段选择逻辑  
SELECT     
    id,      
    DEPARTMENTMARK,      
    supdepid,
	depth,
	path,
    SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 1), ',', -1) AS field1,      
    IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 1, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 2), ',', -1), NULL) AS field2,    
    IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 3), ',', -1), NULL) AS field3,    
    IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 4), ',', -1), NULL) AS field4,    
    IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 5), ',', -1), NULL) AS field5,    
    IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 6), ',', -1), NULL) AS field6    
FROM ranked_departments    
WHERE rn = 1