通过sql实现查树结构的所有子级或父级

 前提

主要利用find_in_set函数，还有就是:=操作符；
不了解的可以去了解一下这两个东西的作用，这里简单说一下：
find_in_set 就是判断以逗号拼接的字符串中是否包含某个字符串，例如 find_in_set('a','a,b,c')，返回的是a在后边集合中的位置，这里就是1了；
:=：意思是设置值，再增改查中都是一样，比如 select @num:=@num+1 ，就是设置num值递增1。

查询所有子集

SELECT
 code,  --orgCode
 PK_ORG, --主键id
 name , --orgName
 ischild --是否子级
FROM
 (
 SELECT
  t1.code,
  t1.PK_ORG,
  t1.name,
 IF
  ( find_in_set( pk_fatherorg, @pids ) > 0, @pids := concat( @pids, ',', PK_ORG ), '0' ) AS ischild  --pk_fatherorg：父级id
 FROM
  ( SELECT code, PK_ORG, name, pk_fatherorg FROM jthr.org_orgs t WHERE dr = 0 ORDER BY code ) t1, --dr：删除标记
  ( SELECT @pids := #{param.pkOrg} ) t2 --@pids：入参，即要查询的组织机构id
 ) t3 
WHERE
 ischild != '0' or PK_ORG = #{param.pkOrg} -- (带本级)
 -- ischild != '0'(不带本级)
 

具体解释以下这个sql，其中难以理解的是在 IF(find_in_set(pk_fatherorg, @pids) > 0, @pids := concat(@pids, ',', PK_ORG), '0') AS ischild，这块理解了这个sql就不复杂了，我们先来看以下几个函数

FIND_IN_SET(str, strlist) ：

• str: 要查找的字符串。
• strlist: 由逗号分隔的字符串列表。

返回值

• 如果 str 在 strlist 中，则返回其位置（从1开始计数）。
• 如果 str 不在 strlist 中，则返回0。
• 如果 str 或 strlist 为 NULL，则返回 NULL。

IF(find_in_set(pk_fatherorg, @pids) > 0, ... , ...):

• IF 函数用于条件判断。
• 条件部分是 find_in_set(pk_fatherorg, @pids) > 0，即检查 pk_fatherorg 是否存在于 @pids 中。
• 如果条件为真（即 pk_fatherorg 存在于 @pids 中），执行第一个参数。
• 如果条件为假（即 pk_fatherorg 不存在于 @pids 中），执行第二个参数

@pids := concat(@pids, ',', PK_ORG):

• 如果 pk_fatherorg 存在于 @pids 中，将当前记录的 PK_ORG 添加到 @pids 中。
• concat(@pids, ',', PK_ORG) 将 @pids 和 PK_ORG 用逗号连接起来。
• @pids := 将连接后的结果赋值给 @pids 变量。

理解完函数的具体意义后，这段sql就容易理解了，首先是find_in_set函数判断父级id是否是我们传的id，如果是，则是我们要查的子级，将其拼接到@pids里，这样我们的@pids就是我们的入参加上其子级的集合，依次向下遍历，即可得到完整的数据

注意：此处还需注意order by排序字段，有层级字段最好拿层级字段

我们还可以使用 WITH RECURSIVE 函数实现

WITH RECURSIVE org_tree (PK_ORG,CODE,NAME)
     AS (
         -- 初始查询：找到初始组织机构
         SELECT PK_ORG,CODE,NAME
         FROM jthr.org_orgs
         WHERE dr = '0' and PK_ORG = #{param.pkOrg}
         UNION ALL
         -- 递归查询：找到子组织机构
         SELECT cd.PK_ORG,cd.CODE,cd.NAME
         FROM jthr.org_orgs cd
         JOIN org_tree dt ON cd.pk_fatherorg = dt.PK_ORG)
SELECT PK_ORG,CODE,NAME
FROM org_tree
ORDER BY CODE

 WITH RECURSIVE org_tree (orgCode) 是 SQL 中定义递归公共表表达式（Recursive Common Table Expression, Recursive CTE）的一部分。它用于处理层次结构或树状数据。它允许你在查询中定义一个递归的公共表表达式，具体来说，WITH RECURSIVE 通常分为两部分：

• 初始查询：定义递归的起点，即最顶层的数据。
• 递归查询：定义如何从当前层级的数据中获取下一层级的数据，直到没有更多的数据可以递归为止。

当前sql中，函数具体含义：

• WITH RECURSIVE：关键字，表示这是一个递归查询。
• org_tree (PK_ORG,CODE,NAME)：定义了一个名为 org_tree 的临时表（CTE），并且指定了该表的三个列 PK_ORG,CODE,NAME。

查询所有父级 

SELECT
	code,
 PK_ORG,
 name ,
	isParent
FROM
	(
	SELECT
		t1.code,
  t1.PK_ORG,
  t1.name,
	IF
		( find_in_set( PK_ORG, @ids ) > 0, @ids := concat( @ids, ',', pk_fatherorg ), '0' ) AS isParent 
	FROM
		( SELECT code, PK_ORG, name, pk_fatherorg FROM jthr.org_orgs t WHERE dr = 0 ORDER BY code desc) t1,
		( SELECT @ids := #{param.pkOrg} ) t2 
	) t3 
WHERE
	isParent != '0';

同样可以使用 WITH RECURSIVE 函数实现

-- 查询所有父分类
with RECURSIVE org_tree (PK_ORG,CODE,NAME,pk_fatherorg)
as (
    select PK_ORG,CODE,NAME,pk_fatherorg 
		from jthr.org_orgs  
		where dr = '0' and PK_ORG = #{param.pkOrg}
    union all
    select t.PK_ORG,t.CODE,t.NAME,t.pk_fatherorg
    from jthr.org_orgs t
             inner join org_tree t2 on t.PK_ORG = t2.pk_fatherorg
)
select
    PK_ORG,CODE,NAME,pk_fatherorg
from org_tree;