专业做网站优化,国际消息新闻,大桥石化集团网站谁做的,普陀网站制作mysql实现递归查询的方法#xff1a;首先创建表#xff0c;并初始化数据#xff1b;然后向下递归#xff0c;利用find_in_set()函数和group_concat()函数、with recursive实现递归查询。
mysql实现递归查询的方法#xff1a;
1、创建表
DROP TABLE IF EXISTS t_areainf…mysql实现递归查询的方法首先创建表并初始化数据然后向下递归利用find_in_set()函数和group_concat()函数、with recursive实现递归查询。
mysql实现递归查询的方法
1、创建表
DROP TABLE IF EXISTS t_areainfo;
CREATE TABLE t_areainfo (id int(11) NOT 0 AUTO_INCREMENT,level int(11) DEFAULT 0,name varchar(255) DEFAULT 0,parentId int(11) DEFAULT 0,status int(11) DEFAULT 0,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT65 DEFAULT CHARSETutf8;2、初始数据
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);
INSERT INTO t_areainfo VALUES (46, 0, xx省1, 1, 0);
INSERT INTO t_areainfo VALUES (47, 0, xx省2, 1, 0);
INSERT INTO t_areainfo VALUES (48, 0, xx省3, 1, 0);
INSERT INTO t_areainfo VALUES (49, 0, xx省4, 1, 0);
INSERT INTO t_areainfo VALUES (50, 0, xx省5, 1, 0);
INSERT INTO t_areainfo VALUES (51, 0, xx省6, 1, 0);
INSERT INTO t_areainfo VALUES (52, 0, xx省7, 1, 0);
INSERT INTO t_areainfo VALUES (53, 0, xx省8, 1, 0);
INSERT INTO t_areainfo VALUES (54, 0, xx省9, 1, 0);
INSERT INTO t_areainfo VALUES (55, 0, xx省10, 1, 0);
INSERT INTO t_areainfo VALUES (56, 0, xx省11, 1, 0);
INSERT INTO t_areainfo VALUES (57, 0, xx省12, 1, 0);
INSERT INTO t_areainfo VALUES (58, 0, xx省13, 1, 0);
INSERT INTO t_areainfo VALUES (59, 0, xx省14, 1, 0);
INSERT INTO t_areainfo VALUES (60, 0, xx省15, 1, 0);
INSERT INTO t_areainfo VALUES (61, 0, xx省16, 1, 0);
INSERT INTO t_areainfo VALUES (62, 0, xx省17, 1, 0);
INSERT INTO t_areainfo VALUES (63, 0, xx省18, 1, 0);
INSERT INTO t_areainfo VALUES (64, 0, xx省19, 1, 0);3、向下递归
a、利用find_in_set()函数和group_concat()函数实现递归查询
DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
DELIMITER ;;
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 ;调用方式
SELECT queryChildrenAreaInfo(1);b、利用with recursive 查询实现向下递归
with recursive temp as (
select * from province p where id 3
union all select t.* from province t inner join temp t2 on t2.id t.parent_id
)
select * from temp 注解
sql中with xxxx as () 是对一个查询子句做别名同时数据库会对该子句生成临时表
with recursive 则是一个递归的查询子句他会把查询出来的结果再次代入到查询子句中继续查询如下面的语句
WITH RECURSIVE d(n, fact) AS (
VALUES (0, 1)
UNION ALL
SELECT n1, (n1)*fact FROM d WHERE n 7
)
SELECT * FROM d结果如下n0; fact1,
n1; fact1,
n2; fact2,
n3; fact6,
n4; fact24,
n5; fact120,
n6; fact720,
n7; fact50405、向上递归 a、利用存储过程循环WHILE
DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
DELIMITER;;
CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp$;
SET sTempChd CAST(areaId AS CHAR);
SET sTemp CONCAT(sTemp,,,sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id sTempChd;
WHILE sTempChd 0 DO
SET sTemp CONCAT(sTemp,,,sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id sTempChd;
END WHILE;
RETURN sTemp;
END
;;
DELIMITER ;调用方式 查询id为7的节点的所有上级节点
SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));b、利用with recursive 查询实现向上递归
with recursive type_cte as (select id,name ,parent_id from province where id 46union allselect t.id,concat(type_cte2.name,,t.name),t.parent_idfrom province tinner join type_cte type_cte2 on t.id type_cte2.parent_id
)
selectid, name, parent_id
from type_cte;
