Menu Close

MariaDB 递归SQL示例

lcc0556

1. 使用 RECURSIVE 关键字实现递归

1.1. 从父到子递归

WITH RECURSIVE depinfo(accdepid,parentid,accdepname,lev,idpath,namepath)
        AS (SELECT a.accdepid,
                   a.parentid,
                   a.accdepname,
                   1 AS lev,
                   CONCAT('/', Cast(a.accdepid AS VARCHAR(4000)),'/') AS idpath,
                   CONCAT('/', a.accdepname,'/') AS namepath
              FROM sc_accdep_bak a
             WHERE a.parentid = -1
               AND a.epid = 1
               -- AND a.remark = '测试递归'
             UNION ALL
            SELECT b.accdepid,
                   b.parentid,
                   b.accdepname,
                   x.lev + 1,
                   CONCAT(x.idpath,Cast(b.accdepid AS VARCHAR(4000)),'/') as idpath,
                   CONCAT(x.namepath,b.accdepname,'/') AS namepath
              FROM depinfo x
             INNER JOIN (SELECT *
                           FROM sc_accdep_bak y
                          WHERE y.epid = 1
                            AND y.parentid <> -1
                            -- AND y.remark = '测试递归'
                        ) b ON x.accdepid = b.parentid)
SELECT accdepid,parentid,accdepname,Lev,idpath,namepath
  FROM depinfo;

1.2. 从子到父递归

WITH RECURSIVE depinfo(accdepid,parentid,accdepname,lev,idpath,namepath)
        AS (SELECT a.accdepid,
                   a.parentid,
                   a.accdepname,
                   1 AS lev,
                   CONCAT('/', Cast(a.accdepid AS VARCHAR(4000)),'/') AS idpath,
                   CONCAT('/', a.accdepname,'/') AS namepath
              FROM sc_accdep_bak a
             WHERE a.accdepid = 577
               AND a.epid = 1
             UNION ALL
            SELECT b.accdepid,
                   b.parentid,
                   b.accdepname,
                   x.lev + 1,
                   CONCAT(x.idpath,Cast(b.accdepid AS VARCHAR(4000)),'/') as idpath,
                   CONCAT(x.namepath,b.accdepname,'/') AS namepath
              FROM depinfo x
             INNER JOIN (SELECT *
                           FROM sc_accdep_bak y
                          WHERE y.epid = 1
                        ) b ON x.parentid = b.accdepid)
SELECT accdepid,parentid,accdepname,Lev,idpath,namepath
  FROM depinfo;

2. 使用变量实现递归

2.1. 从子到父递归

SELECT t2.module_code,
       t2.module_name,
       t2.parent_code,
       t1.lev
  FROM (SELECT @parentCode AS _parentCode,
               (SELECT @parentCode := parent_code FROM ph_module WHERE module_code = _parentCode ) AS supdepCode,
               @level := @level + 1 AS lev
          FROM (SELECT @parentCode := '106010203', @level := 0) vars,
               ph_module) t1
 INNER JOIN ph_module t2 ON t2.module_code = t1._parentCode
 ORDER BY t1.lev DESC;

Post Views: 543
Tagged ,