1. 查询下级机构

1.1 查询下级机构不包含本身

-- 查询下级机构不包含本身 (查询本级及下级将-1 修改为 if(t1.code= '370000000000' ,'370000000000' ,-1))
SELECT
    code 
FROM  
    (
    SELECT t1.code, IF ( find_in_set( parent_code, @ids ) > 0, @ids := concat( @ids, ',', code ), -1) AS ischild 
    FROM 
    ( SELECT code, parent_code FROM sys_region  t WHERE t.flag = '0' ORDER BY parent_code, code ) t1,( SELECT @ids := '370000000000' ) t2 
    ) t3 
WHERE
    ischild != -1

解析：1.找出所有的节点

SELECT code, parent_code FROM sys_region  t 
WHERE t.flag = '0' ORDER BY parent_code, code

image.png

2.如果节点的父id 在选择的节点id中将该节点添加到选择的节点集合中（递归）

    SELECT t1.code,t1.parent_code, t1.level,
            IF ( find_in_set( parent_code, @ids ) > 0, @ids := concat( @ids, ',', code ), -1    ) AS ischild 
    FROM 
    ( SELECT code, parent_code ,level FROM sys_region  t
           WHERE t.flag = '0' ORDER BY parent_code, code ) t1,
      ( SELECT @ids := '370102000000' ) t2

image.png

PS: 红色框起来的依次在增加

3.最后将所有子节点返回

关键方法

find_in_set(str,strList) ：查询字段(strlist)中包含(str)的结果

参数：str 要查询的字符串,strlist 字段名 参数以”,”分隔 如 (1,2,6,8)

结果：不包含——0, 包含——返回第一个存在的位置：select FIND_IN_SET('2', '1,2,6,8'); 结果 2

if（express1,express2,express3）：当exprss1成立时，执行express2,否则执行express3;

1.2查询下级机构，不包含自身

        SELECT * FROM
             (
                SELECT * FROM sys_region 
             ) T1,
             (SELECT @pid := '370102000000' ) T2
        WHERE ((FIND_IN_SET(parent_code,@pid) > 0 AND @pid := CONCAT(@pid, ',', code)));

2. 查询本级及上级

2. 1查询本级及上级

-- 查询本级及上级
SELECT t2.code 
FROM ( 
    SELECT 
        @r AS _code, 
        (SELECT @r := parent_code FROM sys_region WHERE code = _code) AS parent_id
    FROM 
        (SELECT @r := 370171000000) vars, 
        sys_region h 
    WHERE @r <> 0) t1 
JOIN sys_region t2 
ON t1._code = t2.code

image.png
补充：
MySQL中“<>”是什么意思

MySQL中<>是不等号的意思。

sql中有两种方式表示不等于，一种是"<>"(不含引号)，另一种是"!="(不含引号)，用法是一样的。

例如：

解析：

 SELECT 
        @r AS _code, 
        (SELECT @r := parent_code FROM sys_region WHERE code = _code) AS parent_id
    FROM 
        (SELECT @r := 370102000000) vars, 
        sys_region h 
   WHERE @r <> 0

image.png

Ps:

-- 查询机构号
set @queryId = '100007';
-- 执行查询上级机构
SELECT d3.*,d2.lvl
    FROM ( 
        SELECT @r AS id, 
          (SELECT @r := parent_org_id FROM org_info where org_id = @r) AS tmp_parent_id,
                    @l := @l + 1 AS lvl -- 查询出上级机构的顺序（100007为1，直接上级为2，以此类推）
        FROM (SELECT @r := @queryId,@l := 0  ) leafNodeId, 
             org_info hd) d2 
    INNER JOIN org_info d3 ON d2.id = d3.org_id AND d2.tmp_parent_id is not null
ORDER BY d3.org_id;
 
 
-- 或
SELECT _id as orgId,parent_org_id, lvl   FROM (
    SELECT   
        @r AS _id,   
        (SELECT @r := parent_org_id FROM org_info WHERE org_id = _id) AS parent_org_id,   
        @l := @l + 1 AS lvl   
    FROM   
        (SELECT @r := @queryId, @l := 0) vars,   
        org_info h   
        WHERE @r <> 0 and @r <> '' and @r IS NOT NULL
) tmp