递归查询之SQL Server和MySql

1.SQL Server

向下递归：

WITH TREE
     AS (SELECT root.OUID,
                root.OUName,
                root.OUType
         FROM   UT_SYS_OU root
         WHERE  root.ouid = 1
                AND root.IsDeleted = 0
         UNION ALL
         SELECT UT_SYS_OU.ouid,
                UT_SYS_OU.OUName,
                UT_SYS_OU.OUType
         FROM   UT_SYS_OU
                JOIN TREE
                  ON UT_SYS_OU.parentouid = TREE.ouid
                     AND UT_SYS_OU.IsDeleted = 0)
SELECT *
FROM   TREE tree
WHERE  tree.ouType = '1' 

向上递归则需要修改查询条件为：

WITH TREE
     AS (SELECT root.OUID,
                root.OUName,
                root.OUType
         FROM   UT_SYS_OU root
         WHERE  root.ouid = 1
                AND root.IsDeleted = 0
         UNION ALL
         SELECT UT_SYS_OU.ouid,
                UT_SYS_OU.OUName,
                UT_SYS_OU.OUType
         FROM   UT_SYS_OU
                JOIN TREE
                  ON UT_SYS_OU.ouid  = TREE.parentouid
                     AND UT_SYS_OU.IsDeleted = 0)
SELECT *
FROM   TREE tree
WHERE  tree.ouType = '1' 

2.MySql

with x AS(
    SELECT
        code
    FROM
        sys_code_info a START WITH a.code = '7002' CONNECT BY PRIOR a.code = a.parent_code
        AND a.code_type = 'Industry'
)

本示例为向下递归，情况和SQL Server类似，如果向上递归则需要修改关联条件为：a.parent_code=a.code.