mysql实现父子层级查询

表结构：

CREATE TABLE `t_m_org` (
    `org_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '组织ID',
    `org_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '组织名称' COLLATE 'utf8_bin',
    `org_phone` VARCHAR(50) NULL DEFAULT NULL COMMENT '组织电话' COLLATE 'utf8_bin',
    `org_address` VARCHAR(255) NULL DEFAULT NULL COMMENT '组织地址' COLLATE 'utf8_bin',
    `lat` DECIMAL(15,8) NULL DEFAULT NULL COMMENT '纬度',
    `lng` DECIMAL(15,8) NULL DEFAULT NULL COMMENT '经度',
    `org_level` INT(11) NULL DEFAULT NULL COMMENT '组织层级',
    `parent_id` BIGINT(20) NULL DEFAULT NULL COMMENT '父Id',
    `is_delete` INT(11) NULL DEFAULT NULL COMMENT '是否删除(0:未删除；1:已删除)',
    `org_status` INT(11) NULL DEFAULT NULL COMMENT '状态(1:正常；2:装修；3:倒闭停业)',
    `add_time` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `customer_id` BIGINT(20) NOT NULL COMMENT '客户ID',
    PRIMARY KEY (`org_id`)
)
COMMENT='组织表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10
;

插入数据：

 

1,通过ID获取子集ID，查询sql（@pids := 查询的父级ID）

SELECT
    c.org_id
FROM
    (
        SELECT
            a.org_id,
        IF (
            FIND_IN_SET(a.parent_id ,@pids) > 0,

        IF (
            length(@pids) - length(
                REPLACE (@pids, a.parent_id, '')
            ) > 1,

        IF (
            length(@pids) - length(REPLACE(@pids, a.org_id, '')) > 1 ,@pids ,@pids := concat(@pids, ',', a.org_id)
        ) ,@pids := concat(@pids, ',', a.org_id)
        ),
        0
        ) AS 'plist',

    IF (
        FIND_IN_SET(a.parent_id ,@pids) > 0,
        @pids,
        0
    ) AS ischild
    FROM
        (
            SELECT
                r.org_id,
                r.parent_id
            FROM
                t_m_org r
        ) a,
        (SELECT @pids := 1) b
    ) c
WHERE
    c.ischild != 0

效果：

注：如果想把父级ID也加上 在上面SQL的最上面加上SELECT 1 FROM DUAL UNION ALL (1为父级ID)

原文地址：

https://blog.csdn.net/weixin_41876599/article/details/82911609

查询所有父级，包括自己

SELECT T2.id,T2.share_user_id
                FROM ( 
                    SELECT 
                            @r AS _id, 
                            (SELECT @r := share_user_id FROM dts_user WHERE id = _id) AS parent_id , 
                             @l := @l + 1 AS lvl 
                    FROM 
                            (SELECT @r := #{id}, @l := 0) vars, 
                            dts_user h 
                    WHERE @r <> 0) T1 
                JOIN dts_user T2 
        ON T1._id = T2.id
ORDER BY id;

查询所有子级，不包含自己

select id,share_user_id from (
              select t1.id,t1.share_user_id,
              if(find_in_set(share_user_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
              from (
                   select id,share_user_id from dts_user t  order by share_user_id, id
                  ) t1,
                  (select @pids := #{id}) t2
             ) t3 where ischild != 0