CREATE TABLE `digui_test` (
`org_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘子公司‘,
`inv_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘母公司‘
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE DEFINER=`root`@`localhost` FUNCTION `getAllChild`(rootId varchar(16380)) RETURNS varchar(16380) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    DETERMINISTIC
BEGIN 
    DECLARE pTemp VARCHAR(16380);            
    DECLARE cTemp VARCHAR(16380);      -- 节点ID(临时变量)

    SET pTemp = ‘$‘;  
    SET cTemp =cast(rootId as CHAR);  -- 把rootId强制转换为字符。

    WHILE cTemp is not null DO  
       SET pTemp = concat(pTemp,‘,‘,cTemp);  -- 把所有节点连接成字符串。
       SELECT group_concat(org_id) INTO cTemp FROM test.digui_test   
       WHERE FIND_IN_SET(inv_id,cTemp)>0; 
    END WHILE;  
    RETURN pTemp;  
END

select 
 distinct  org_id
from 
(
    SELECT
        org_id
    FROM 
             test.digui_test  T,
            (SELECT @DATAS := getAllChild (‘bx001‘)) x 
    WHERE find_in_set (org_id, @DATAS)
    )a