最新在实习过程中多次遇到了一个 sql 应用场景：在一个存储了所有上下级组织的组织树的表中，只有一小部分组织有特殊标识，但是我们要找到这些组织的所有下级组织。这很明显是一个递归的操作，要拿上级组织的B00（组织ID）与下级组织的B0144B（上级组织ID）一一对应，直到匹配不到下级组织（即递归到底了），但是这个递归方式在不同的 SQL 场景中有不同的实现方式：
Oracle/达梦
在 Oracle 中通常使用START WITH …… CONNECT BY ……语法来实现这一功能。
首先定义一个通用场景：
假设有一个组织表B00，有以下字段
B00：组织IDB0101： 组织名称B0144B：上级组织ID
START WITH …… CONNECT BY ……中，使用 START WITH 定义递归的根节点，使用 CONNECT BY 定义递归逻辑，下面是一个由根节点直接向下遍历的示例：
SELECT
LEVEL, -- Oracle提供的伪列，表示层级深度（1, 2, 3...）
LPAD(' ', 2*(LEVEL-1)) || B0101 AS TREE_NAME, --利用LEVEL做缩进显示
B00,
B0144B
FROM B00
START WITH B0144B IS NULL -- 从 MGR 为空的节点开始（KING）
CONNECT BY PRIOR B00 = B0144B ; -- 关键逻辑上面还使用了另一个关键字：PRIOR 他表示这是上级的字段 ，CONNECT BY PRIOR B00 = B0144B 表示上级的B00 = 下级的 B0144B ，表示这是一个从上级往下级找的逻辑。
如果现在的 START WITH 改变了，使一个根节点（最下级组织）作为起始节点，然后我们想找出他的上级节点，我们就可以修改 CONNECT BY 中的上级侧 : RPIOR B0144B = B00 （表示下级的 B00 等于上级的 B0144B）
另外在上面的 SELECT 中，我们还使用了一个 LEVEL ，这并不是我们表中的有的字段，而是 Oracle 在遍历场景下特意为我们提供的一个位列，作用是返回当前行在树中的深s度。根节点为 1，子节点为2……
除了 LEVEL 外，Oracle 还提供了几个在递归查询中为我们提供遍历的位列和方法：
- SYS_CONNECT_BY_PATH(B0101,'/')
显示从根节点导出当前节点的完整路径（第一个参数是每条数据用于拼接的字段，第二个参数是两个字段之间用于拼接的内容） - CONNECT_BY_ISLEAF
判断当前节点是否是叶子节点（1代表是叶子、0代表不是） - CONNECT_BY_ROOT
当前节点所属的根节点的数据
Mysql 8.0+ / PSql
mysql 中可以使用 WITH RECURSIVE 和 UNION ALL 来实现，实现模板如下：
WITH RECURSIVE cte_name (列名列表) AS (
-- 1. 初始查询 (START WITH)
SELECT ...
FROM table_name
WHERE 初始条件
UNION ALL
-- 2. 递归查询 (CONNECT BY)
SELECT ...
FROM table_name t
INNER JOIN cte_name c ON t.父ID = c.子ID -- 这里定义连接关系
)
SELECT * FROM cte_name;在 mysql 中，第一个 select 语句用于定义初始条件；第二个 select 语句的 inner join 用于定义连接关系；这里的 c.ID 对应了 Oracle 中的 PRIOR 项，表示上级记录。
同样使用上面的 B00 表，做一个从根目录往下查询的示例：
WITH RECURSIVE b00_tree AS (
-- part 1: 锚点 (Start With) - 找到大老板
SELECT
b00,
b0101,
b0144b,
1 AS lvl -- 手动初始化 Level 为 1
FROM b00
WHERE b0144b IS NULL
UNION ALL
-- part 2: 递归 (Connect By) - 拿着上一层的结果(c) 去找下一层(t)
SELECT
t.b00,
t.b0101,
t.b0144b,
c.lvl + 1 -- 每次递归 Level + 1
FROM emp t
INNER JOIN b00_tree c ON t.b0144b = c.b00 -- 这里的 Join 等同于 PRIOR EMPNO = MGR
)
SELECT * FROM b00_tree ORDER BY lvl, b00;Mysql 5.7
1.7版本的mysql没有提供可用的语法。但是我又确确实实遇到了要匹配父组织的应用场景。于是就使用了一种邪修：如何确定一个组织的顶级父组织？可以获取该组织的父组织ID、父父组织ID、父父父组织ID、父父父父组织ID，然后分别对已知的顶级父组织 ID 进行对比，只要匹配上了，就表示这是他的顶级父组织。
实战场景：
SELECT
a24.ZDYA2203 AS DMCOD,
COUNT(DISTINCT a24.a00) AS pCount,
COUNT(DISTINCT CASE WHEN a01.ZDYXA0176 = 'lzpID' THEN a01.a00 END) AS lzpCount,
COUNT(DISTINCT CASE WHEN a01.ZDYXA0176 = 'bspID' THEN a01.a00 END) AS bspCount,
COUNT(DISTINCT CASE WHEN a01.ZDYXA0176 = 'zzpID' THEN a01.a00 END) AS zzpCount,
COUNT(DISTINCT CASE WHEN a01.ZDYXA0176 = 'nhpID' THEN a01.a00 END) AS nhpCount,
FROM a24
INNER JOIN a01 ON a01.A00 = a24.A00
WHERE a24.DELETEFLAG = 0
GROUP BY a24.ZDYA2203这段代码本意是想统计每个部分下的人数汇总，但是这样写只能统计顶级部门，mysql 1.7 又不允许递归查询，并且我能确定，每个部门写的分级不超过五级，于是代码就变成了这样：
SELECT
a24.ZDYA2203 AS DMCOD,
COUNT(DISTINCT a24.a00) AS pCount,
-- 核心修改：判断目标 ID 是否在人员的“祖先树”中（L1是本级，L2是父级，以此类推）
COUNT(DISTINCT CASE WHEN 'lzpID' IN (L1.B00, L2.B00, L3.B00, L4.B00, L5.B00, L6.B00) THEN a01.a00 END) AS lzpCount,
COUNT(DISTINCT CASE WHEN 'bspID' IN (L1.B00, L2.B00, L3.B00, L4.B00, L5.B00, L6.B00) THEN a01.a00 END) AS bspCount,
COUNT(DISTINCT CASE WHEN 'zzpID' IN (L1.B00, L2.B00, L3.B00, L4.B00, L5.B00, L6.B00) THEN a01.a00 END) AS zzpCount,
COUNT(DISTINCT CASE WHEN 'nhpID' IN (L1.B00, L2.B00, L3.B00, L4.B00, L5.B00, L6.B00) THEN a01.a00 END) AS nhpCount,
FROM a24
INNER JOIN a01 ON a01.A00 = a24.A00
LEFT JOIN B01 L1 ON L1.B00 = a01.ZDYXA0176 -- 本级
LEFT JOIN B01 L2 ON L2.B00 = L1.B0144B -- 父级
LEFT JOIN B01 L3 ON L3.B00 = L2.B0144B -- 祖父级
LEFT JOIN B01 L4 ON L4.B00 = L3.B0144B -- 曾祖父级
LEFT JOIN B01 L5 ON L5.B00 = L4.B0144B -- 高祖父级
LEFT JOIN B01 L6 ON L6.B00 = L5.B0144B -- 天祖父级
WHERE a24.DELETEFLAG = 0
GROUP BY a24.ZDYA2203通过找上级部门的 ID 进行范围匹配，来实现一样的效果！
Comments NOTHING