mysql查询树结构

--创建数据表





DROPTABLE IF EXISTS `t_areainfo`;

CREATETABLE `t_areainfo` (

 `id`int(11)NOTNULL AUTO_INCREMENT,

 `level`int(11)DEFAULT0,

 `name`varchar(255),

 `parentId`int(11),

 `status`int(11)DEFAULT0,

 PRIMARYKEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULTCHARSET=utf8;

 

--给表插入数据

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

 

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

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

 

delimiter //

DROPFUNCTION IF EXISTS queryChildrenAreaInfo;

CREATEFUNCTION `queryChildrenAreaInfo` (areaId INT)

RETURNSVARCHAR(4000)

BEGIN

DECLAREsTemp VARCHAR(4000);

DECLAREsTempChd VARCHAR(4000);

 

SETsTemp = '$';

SETsTempChd = cast(areaIdaschar);

 

WHILE sTempChd isnot NULL DO

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

SELECTgroup_concat(id) INTOsTempChd FROMt_areainfo whereFIND_IN_SET(parentId,sTempChd)>0;

ENDWHILE;

returnsTemp;

END

//

delimiter //

 

 

--调用function函数方式

selectqueryChildrenAreaInfo(1);

select* fromt_areainfo whereFIND_IN_SET(id, queryChildrenAreaInfo(20));