Database: sql recursive query

Posted by Gummie on Fri, 03 Jan 2020 21:43:06 +0100

 

mysql self association table, the following are examples of downward recursion and upward recursion.

1. Prepare for recursive query. If your table already exists, this step can be ignored.

  • Building tables
CREATE TABLE `wq_areainfo` (
 `id` int(11) NOT null 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`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;
  • Initialization data
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (1, 0, 'China', 0, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (2, 0, 'North China', 1, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (3, 0, 'Southern China District', 1, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (4, 0, 'Beijing', 2, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (5, 0, 'Haidian District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (6, 0, 'Fengtai District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (7, 0, 'Chaoyang District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (8, 0, 'Daxing District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (9, 0, 'Dongcheng District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (10, 0, 'Xicheng District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (11, 0, 'Chongwen District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (12, 0, 'Xuanwu District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (13, 0, 'Shijingshan District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (14, 0, 'Mentougou District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (15, 0, 'Fangshan District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (16, 0, 'Tongzhou District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (17, 0, 'Shunyi District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (18, 0, 'Changping District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (19, 0, 'Huairou District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (20, 0, 'Pinggu District', 4, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (46, 0, 'Jilin Province', 1, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (47, 0, 'Heilongjiang', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (48, 0, 'Harbin', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (49, 0, 'Dalian', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (50, 0, 'Shenyang', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (63, 0, 'Songyuan', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (64, 0, 'Jilin City', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (65, 0, 'Huludao', 46, 0);
INSERT INTO `t_areainfo` (`id`, `level`, `name`, `parentId`, `status`) VALUES (66, 0, 'Fuyu', 46, 0);

2. Recursively query all child nodes and child nodes under the current node

  • Using find in set() function and group concat() function to realize recursive query
    Where: group_concat(): multiple records compose one record; find_in_set (str, strlist): query specific columns in multiple records, str string to query, strlist field name parameters are separated by ",", such as (1,2,6,8):
DROP FUNCTION IF EXISTS queryChildren;
CREATE FUNCTION queryChildren(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;
  • Calling mode
SELECT queryChildren(1);
  • 1

 
-Query all nodes with id "4"

SELECT * FROM wq_areainfo WHERE FIND_IN_SET(id,queryChildren(4));
  • 1

3 recursively query all fathers and their fathers

DROP FUNCTION IF EXISTS queryParents;
CREATE FUNCTION queryParents(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;
  • Query the father with id 47 and the father's father, and query the previous generation infinitely recursively.  
    SELECT * from t_areainfo where FIND_IN_SET(id,queryParents(47)); 

The efficiency of database recursive query is low, which is a way to realize data query, but it is not recommended to use for more data

 

Reprint - https://blog.csdn.net/qq_17033579/article/details/82216035

Topics: MySQL Database

Hot Topics

©2024 Programmer Think