查询泛微OA中完整的组织架构

很多时候要用到组织架构，比如矩阵编辑、行政发文等，我们可以直接从数据库中查询获得，泛微具有公司、分部、部门3个维度，在该SQL中，不同维度使用||符号进行连接，其余的使用>符号进行连接。

WITH subcom_cte (id, subcompanyname, companyid, supsubcomid, canceled, level, path) AS (
    SELECT id, subcompanyname, companyid, supsubcomid, canceled, 1,
        CAST(subcompanyname AS nvarchar(max))
    FROM hrmsubcompany
    WHERE supsubcomid = 0 AND canceled = 0
    UNION ALL
    SELECT s.id, s.subcompanyname, s.companyid,
        s.supsubcomid,
        s.canceled,
        c.level + 1,
        CAST(c.path + ' > ' + s.subcompanyname AS nvarchar(max))
    FROM hrmsubcompany s
    JOIN subcom_cte c ON s.supsubcomid = c.id
    WHERE s.canceled = 0
),
dep_cte (id,
         departmentname,
         subcompanyid1,
         supdepid,
         canceled,
         level,
         path) AS (
             SELECT id,
                    departmentname,
                    subcompanyid1,
                    supdepid,
                    canceled,
                    1,
                    CAST(departmentname AS nvarchar(max))
             FROM hrmdepartment
             WHERE supdepid = 0 AND canceled = 0
             UNION ALL
             SELECT d.id,
                    d.departmentname,
                    d.subcompanyid1,
                    d.supdepid,
                    d.canceled,
                    c.level + 1,
                    CAST(c.path + ' > ' + d.departmentname AS nvarchar(max))
             FROM hrmdepartment d
             JOIN dep_cte c ON d.supdepid = c.id
             WHERE d.canceled = 0)
SELECT (co.companyname + ' || ' + sc.path + ' || ' + d.path) AS 组织架构
FROM HrmCompany co
JOIN subcom_cte sc ON co.id = sc.companyid
JOIN dep_cte d ON sc.id = d.subcompanyid1 ORDER BY 组织架构;