NC65 sql server实现类似于Oracle中start with……connect by prior……的递归查询方法

NC65 sql server实现类似于Oracle中start with……connect by prior……的递归查询方法

查询树型结构某节点的所有上级根节点

--查询树型结构某节点的所有上级根节点
with eps_root(pk_eps, pk_parent, eps_code, eps_name) as (
    SELECT
        pk_eps,
        pk_parent,
        eps_code,
        eps_name

    FROM pm_eps
    where pk_eps = '1001A11000000003P61H'--查询条件为树型结构某子节点

    union all

    --递归条件
    SELECT
        e.pk_eps,
        e.pk_parent,
        e.eps_code,
        e.eps_name

    FROM
        pm_eps e
    inner join eps_root r--执行递归
        on e.pk_eps = r.pk_parent--根据基础表条件查询子节点（e.pk_eps）
)

select * from eps_root;

如图，查询“01030701 在职员工其他福利费”节点的所有上级节点

sql执行结果如图：

查询树型结构某节点下所有的子节点

--查询树型结构某节点下所有的子节点
with eps_child(pk_eps, pk_parent, eps_code, eps_name)
as (
    --起始条件
        select
            pk_eps,
            pk_parent,
            eps_code,
            eps_name
        from pm_eps
        where pk_eps = '1001A11000000003P60K'--列出某父节点查询条件

        union all

        --递归条件
        select
            e.pk_eps,
            e.pk_parent,
            e.eps_code,
            e.eps_name
        from pm_eps e
        inner join eps_child c
        on e.pk_parent = c.pk_eps--根据查询到的父节点（e.pk_parent）
)
select * from eps_child;

如图，查询出“0103 人工成本” 下所有的子节点：

sql语句执行结果如下：

oracle与DB2递归查询sql语句
一、DB2 通过WITH AS递归查询

with dept (pk_dept, dept_code, dept_name, pk_fatherorg)

        AS (select pk_dept, dept_code, dept_name, pk_fatherorg

              FROM org_dept

             WHERE pk_dept = '1001A110000000000NRY'

            UNION ALL

            select pk_dept, dept_code, dept_name, pk_fatherorg
            
              FROM org_dpet parent, dept d

             WHERE parent.pk_fatherorg= d.pk_dept)

select pk_dept, dept_code, dept_name, pk_fatherorg from dept;

oracle 通过 start with connect by prior 递归查询

select * from org_dept start with pk_dept = '1001A110000000000NRY' connect by prior pk_dept = pk_fatherorg;