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));