mysql查询树结构

--创建数据表





DROP TABLE IF EXISTS `t_areainfo`;

CREATE TABLE `t_areainfo` (

  `id` int (11) NOT NULL AUTO_INCREMENT,

  ` level ` int (11) DEFAULT 0,

  ` name ` varchar (255),

  `parentId` int (11),

  `status` int (11) DEFAULT 0,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

 

--给表插入数据

 

INSERT INTO `t_areainfo` VALUES ( '1' , '0' , '中国' , '0' , '0' );

INSERT INTO `t_areainfo` VALUES ( '2' , '0' , '华北区' , '1' , '0' );

INSERT INTO `t_areainfo` VALUES ( '3' , '0' , '华南区' , '1' , '0' );

INSERT INTO `t_areainfo` VALUES ( '4' , '0' , '北京' , '2' , '0' );

INSERT INTO `t_areainfo` VALUES ( '5' , '0' , '海淀区' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '6' , '0' , '丰台区' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '7' , '0' , '朝阳区' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '8' , '0' , '北京XX区1' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '9' , '0' , '北京XX区2' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '10' , '0' , '北京XX区3' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '11' , '0' , '北京XX区4' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '12' , '0' , '北京XX区5' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '13' , '0' , '北京XX区6' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '14' , '0' , '北京XX区7' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '15' , '0' , '北京XX区8' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '16' , '0' , '北京XX区9' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '17' , '0' , '北京XX区10' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '18' , '0' , '北京XX区11' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '19' , '0' , '北京XX区12' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '20' , '0' , '北京XX区13' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '21' , '0' , '北京XX区14' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '22' , '0' , '北京XX区15' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '23' , '0' , '北京XX区16' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '24' , '0' , '北京XX区17' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '25' , '0' , '北京XX区18' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '26' , '0' , '北京XX区19' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '27' , '0' , '北京XX区1' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '28' , '0' , '北京XX区2' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '29' , '0' , '北京XX区3' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '30' , '0' , '北京XX区4' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '31' , '0' , '北京XX区5' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '32' , '0' , '北京XX区6' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '33' , '0' , '北京XX区7' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '34' , '0' , '北京XX区8' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '35' , '0' , '北京XX区9' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '36' , '0' , '北京XX区10' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '37' , '0' , '北京XX区11' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '38' , '0' , '北京XX区12' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '39' , '0' , '北京XX区13' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '40' , '0' , '北京XX区14' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '41' , '0' , '北京XX区15' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '42' , '0' , '北京XX区16' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '43' , '0' , '北京XX区17' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '44' , '0' , '北京XX区18' , '4' , '0' );

INSERT INTO `t_areainfo` VALUES ( '45' , '0' , '北京XX区19' , '4' , '0' );

 

 

--方式一：采用function获取所有子节点的id

--查询传入areaId及其以下所有子节点

 

delimiter //

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION `queryChildrenAreaInfo` (areaId INT )

RETURNS VARCHAR (4000)

BEGIN

DECLARE sTemp VARCHAR (4000);

DECLARE sTempChd VARCHAR (4000);

 

SET sTemp = '$' ;

SET sTempChd = cast (areaId as char );

 

WHILE sTempChd is not NULL DO

SET sTemp = CONCAT(sTemp, ',' ,sTempChd);

SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId,sTempChd)>0;

END WHILE;

return sTemp;

END

//

delimiter //

 

 

--调用 function函数 方式

select queryChildrenAreaInfo(1);

select * from t_areainfo where FIND_IN_SET(id, queryChildrenAreaInfo(20));