# MyBatis树形结构查询

### 1.数据准备

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS area;
CREATE TABLE area (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK',
area_name varchar(50) NOT NULL COMMENT '地区名称',
parent_id int(11) NOT NULL COMMENT '父Id',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=7668 DEFAULT CHARSET=utf8 COMMENT='地区表';

-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO area VALUES ('1', '广东省', '0');
INSERT INTO area VALUES ('2', '深圳市', '1');
INSERT INTO area VALUES ('3', '广州市', '1');
INSERT INTO area VALUES ('4', '湖南省', '0');
INSERT INTO area VALUES ('5', '长沙市', '4');
INSERT INTO area VALUES ('6', '株洲市', '4');


### 2.实体类

public class Area {
private Integer id;

private String areaName;

private Integer parentId;

List<Area> children;

//getter setter
}


### 3.查询sql

SELECT
t_top.id AS id1,
t_top.area_name AS area_name1,
t_top.parent_id AS parent_id1,
t_second.id AS id2,
t_second.area_name AS area_name2,
t_second.parent_id AS parent_id2
FROM
(
SELECT
id,
area_name,
parent_id
FROM
area
WHERE
parent_id = 0
) AS t_top
INNER JOIN (
SELECT
id,
area_name,
parent_id
FROM
area
WHERE
parent_id IN (
SELECT
id
FROM
area
WHERE
parent_id = 0
)
) AS t_second ON t_top.id = t_second.parent_id


### 4.ResultMap映射

  <resultMap id="TreeResultMap" type="com.domain.Area" >
<id column="id1" property="id" jdbcType="INTEGER" />
<result column="area_name1" property="areaName" jdbcType="VARCHAR" />
<result column="parent_id1" property="parentId" jdbcType="INTEGER" />
<collection property="children" ofType="com.domain.Area">
<id column="id2" property="id" jdbcType="INTEGER" />
<result column="area_name2" property="areaName" jdbcType="VARCHAR" />
<result column="parent_id2" property="parentId" jdbcType="INTEGER" />
</collection>
</resultMap>


02-06 1万+

06-25 1228
05-10 250
07-01 2968
11-12 3074
09-28 1607
07-29 352
09-29 377
02-06 1889
10-03
11-02 2503
05-25 2万+
11-29
09-08 1万+
03-13 517
05-30