MySQL查询树结构子节点

说明

以行政区划为例，查询MySQL树结构子节点。

建表

CREATE TABLE `region` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `region_name` varchar(50) NOT NULL COMMENT '区划名称',
  `region_code` char(12) NOT NULL DEFAULT '' COMMENT '区划编码',
  `p_region_code` char(12) NOT NULL DEFAULT '' COMMENT '上级区划编码',
  `region_level` int(255) NOT NULL DEFAULT '-1' COMMENT '区划级别（0：国家；1：省级；2：市级；3：县；4：镇；5：村委会；）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (1, '中国', '000000000000', '', 0);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (2, '北京市', '110000000000', '000000000000', 1);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (3, '市辖区', '110100000000', '110000000000', 2);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (4, '东城区', '110101000000', '110100000000', 3);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (5, '东华门街道', '110101001000', '110101000000', 4);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (6, '多福巷社区居委会', '110101001001', '110101001000', 5);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (7, '银闸社区居委会', '110101001002', '110101001000', 5);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (8, '东厂社区居委会', '110101001005', '110101001000', 5);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (9, '景山街道', '110101002000', '110101000000', 4);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (10, '隆福寺社区居委会', '110101002001', '110101002000', 5);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (11, '吉祥社区居委会', '110101002002', '110101002000', 5);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (12, '西城区', '110102000000', '110100000000', 3);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (13, '西长安街街道', '110102001000', '110102000000', 4);
INSERT INTO region (id, region_name, region_code, p_region_code, region_level) VALUES (14, '府右街南社区居委会', '110102001004', '110102001000', 5);

查询语句

SELECT region_code
FROM (
  SELECT
    region_code,
    IF(FIND_IN_SET(p_region_code, @p_region_code_list) > 0, @p_region_code_list := CONCAT(@p_region_code_list, ',', region_code), '0') AS is_child
  FROM (
    SELECT
      region_code,
      p_region_code
    FROM region
    ORDER BY region_level, region_code
  ) t1, (
    SELECT @p_region_code_list := '110101001000'
  ) t2
) t3
WHERE t3.is_child != '0'

原文地址： https://www.cnblogs.com/zhe-zhangheng/articles/16348369.html