Mysql recursive query tree table and mysql recursive tree table

Source: Internet
Author: User

Mysql recursive query tree table and mysql recursive tree table

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', 'China', '0 ', '0'); insert into't _ areainfo 'values ('2', '0', 'North China ', '1', '0 '); insert into't _ areainfo 'values ('3', '0', 'South China region', '1', '0 '); insert into't _ areainfo 'values ('4', '0', 'beijing', '2', '0 '); insert into't _ areainfo 'values ('5', '0', 'haidian district ', '4', '0 '); insert into't _ areainfo 'values ('6', '0', 'fengtai district ', '4', '0 '); insert into't _ areainfo 'values ('7', '0', 'chaoyang district ', '4', '0 '); insert into't _ areainfo 'values ('8', '0', 'beijingxx zone 1', '4', '0 '); insert into't _ areainfo 'values ('9', '0', 'Beijing XX zone 2', '4', '0 '); insert into't _ areainfo 'values ('10', '0', 'Beijing XX district 3', '4', '0 '); insert into't _ areainfo 'values ('11', '0', 'Beijing XX district 4', '4', '0 '); insert into't _ areainfo 'values ('12', '0', 'Beijing XX district 5', '4', '0 '); insert into't _ areainfo 'values ('13', '0', 'Beijing XX zone 6', '4', '0 '); insert into't _ areainfo 'values ('14', '0', 'Beijing XX district 7', '4', '0 '); insert into't _ areainfo 'values ('15', '0', 'Beijing XX district 8', '4', '0 '); insert into't _ areainfo 'values ('16', '0', 'Beijing XX district 9', '4', '0 '); insert into't _ areainfo 'values ('17', '0', 'Beijing XX district 10', '4', '0 '); insert into't _ areainfo 'values ('18', '0', 'Beijing XX 11', '4', '0 '); insert into't _ areainfo 'values ('19', '0', 'Beijing XX zone 12', '4', '0 '); insert into't _ areainfo 'values ('20', '0', 'Beijing XX district 13', '4', '0 '); insert into't _ areainfo 'values ('21', '0', 'Beijing XX district 14', '4', '0 '); insert into't _ areainfo 'values ('22', '0', 'Beijing XX district 15', '4', '0 '); insert into't _ areainfo 'values ('23', '0', 'Beijing XX district 16', '4', '0 '); insert into't _ areainfo 'values ('24', '0', 'Beijing XX zone 17', '4', '0 '); insert into't _ areainfo 'values ('25', '0', 'Beijing XX district 18', '4', '0 '); insert into't _ areainfo 'values ('26', '0', '19', '4', '0 '); insert into't _ areainfo 'values ('27', '0', 'beijingxx zone 1', '4', '0 '); insert into't _ areainfo 'values ('28', '0', 'Beijing XX zone 2', '4', '0 '); insert into't _ areainfo 'values ('29', '0', 'Beijing XX district 3', '4', '0 '); insert into't _ areainfo 'values ('30', '0', 'Beijing XX district 4', '4', '0 '); insert into't _ areainfo 'values ('31', '0', 'Beijing XX district 5', '4', '0 '); insert into't _ areainfo 'values ('32', '0', 'Beijing XX zone 6', '4', '0 '); insert into't _ areainfo 'values ('33', '0', 'Beijing XX district 7', '4', '0 '); insert into't _ areainfo 'values ('34', '0', 'Beijing XX district 8', '4', '0 '); insert into't _ areainfo 'values ('35', '0', 'Beijing XX district 9', '4', '0 '); insert into't _ areainfo 'values ('36 ', '0', 'Beijing XX district 10', '4', '0 '); insert into't _ areainfo 'values ('37', '0', 'Beijing XX 11', '4', '0 '); insert into't _ areainfo 'values ('38', '0', 'Beijing XX zone 12', '4', '0 '); insert into't _ areainfo 'values ('39 ', '0', 'Beijing XX district 13', '4', '0 '); insert into't _ areainfo 'values ('40', '0', 'Beijing XX district 14', '4', '0 '); insert into't _ areainfo 'values ('41 ', '0', 'Beijing XX district 15', '4', '0 '); insert into't _ areainfo 'values ('42', '0', 'Beijing XX district 16', '4', '0 '); insert into't _ areainfo 'values ('43 ', '0', 'Beijing XX zone 17', '4', '0 '); insert into't _ areainfo 'values ('44', '0', 'Beijing XX district 18', '4', '0 '); insert into't _ areainfo 'values ('45', '0', '19', '4', '0 '); insert into't _ areainfo 'values ('46', '0', 'xx province 1', '1', '0 '); insert into't _ areainfo 'values ('47', '0', 'xx province 2', '1', '0 '); insert into't _ areainfo 'values ('48', '0', 'xx province 3', '1', '0 '); insert into't _ areainfo 'values ('49', '0', 'xx province 4', '1', '0 '); insert into't _ areainfo 'values ('50', '0', 'xx province 5', '1', '0 '); insert into't _ areainfo 'values ('51', '0', 'xx province 6', '1', '0 '); insert into't _ areainfo 'values ('52 ', '0', 'xx province 7', '1', '0 '); insert into't _ areainfo 'values ('53', '0', 'xx province 8', '1', '0 '); insert into't _ areainfo 'values ('54', '0', 'xx province 9', '1', '0 '); insert into't _ areainfo 'values ('55', '0', 'xx province 10', '1', '0 '); insert into't _ areainfo 'values ('56', '0', 'xx province 11', '1', '0 '); insert into't _ areainfo 'values ('57 ', '0', 'xx province 12', '1', '0 '); insert into't _ areainfo 'values ('58 ', '0', 'xx province 13', '1', '0 '); insert into't _ areainfo 'values ('59', '0', 'xx province 14', '1', '0 '); insert into't _ areainfo 'values ('60', '0', 'xx province 15', '1', '0 '); insert into't _ areainfo 'values ('61 ', '0', 'xx province 16', '1', '0 '); insert into't _ areainfo 'values ('62 ', '0', 'xx province 17', '1', '0 '); insert into't _ areainfo 'values ('63 ', '0', 'xx province 18', '1', '0 '); insert into't _ areainfo 'values ('64', '0', 'xx province 19', '1', '0'); -- Method 1: use function to obtain the IDs of all subnodes -- query the input areaId and all its subnodes delimiter // drop function if exists queryChildrenAreaInfo; create function 'querychildrenareainfo' (areaId INT) returns varchar (4000) BEGINDECLARE sTemp VARCHAR (4000); DECLARE sTempChd VARCHAR (4000); SET sTemp = '$'; SET sTempChd = cast (areaId as char ); WHILE sTempChd is not null doset 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 // 2 -- call method select queryChildrenAreaInfo (1); select * from t_areainfo where FIND_IN_SET (id, queryChildrenAreaInfo (20 )); /* delimiter // create function 'delimiter' (rootId INT) RETURNS varchar (1000) BEGINDECLARE sTemp VARCHAR (1000); DECLARE sTempChd VARCHAR (1000); SET sTemp = '$ '; SET sTempChd = cast (rootId as CHAR); WHILE sTempChd is not null DOSET 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 ;*/

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.