mysql實現(xiàn)遞歸查詢的方法示例

更新時間：2023年07月25日 08:51:40 作者：科學熊

本文主要介紹了mysql實現(xiàn)遞歸查詢的方法示例，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

1、創(chuàng)建表

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`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8;

2、初始數(shù)據(jù)

INSERT INTO `t_areainfo` VALUES ('1', '0', '中國', '0', '0');
INSERT INTO `t_areainfo` VALUES ('2', '0', '華北區(qū)', '1', '0');
INSERT INTO `t_areainfo` VALUES ('3', '0', '華南區(qū)', '1', '0');
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀區(qū)', '4', '0');
INSERT INTO `t_areainfo` VALUES ('6', '0', '豐臺區(qū)', '4', '0');
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝陽區(qū)', '4', '0');
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX區(qū)1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX區(qū)2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX區(qū)3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX區(qū)4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX區(qū)5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX區(qū)6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX區(qū)7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX區(qū)8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX區(qū)9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX區(qū)10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX區(qū)11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX區(qū)12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX區(qū)13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX區(qū)14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX區(qū)15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX區(qū)16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX區(qū)17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX區(qū)18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX區(qū)19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX區(qū)1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX區(qū)2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX區(qū)3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX區(qū)4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX區(qū)5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX區(qū)6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX區(qū)7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX區(qū)8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX區(qū)9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX區(qū)10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX區(qū)11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX區(qū)12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX區(qū)13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX區(qū)14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX區(qū)15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX區(qū)16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX區(qū)17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX區(qū)18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX區(qū)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()函數(shù)和group_concat()函數(shù)實現(xiàn)遞歸查詢：

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 查詢實現(xiàn)向下遞歸

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 () 是對一個查詢子句做別名，同時數(shù)據(jù)庫會對該子句生成臨時表；
with recursive 則是一個遞歸的查詢子句，他會把查詢出來的結果再次代入到查詢子句中繼續(xù)查詢，如下面的語句
WITH RECURSIVE d(n, fact) AS (
VALUES (0, 1)
UNION ALL
SELECT n+1, (n+1)*fact FROM d WHERE n < 7
)
SELECT * FROM d
結果如下：
"n=0; fact=1",
"n=1; fact=1",
"n=2; fact=2",
"n=3; fact=6",
"n=4; fact=24",
"n=5; fact=120",
"n=6; fact=720",
"n=7; fact=5040"

4、向上遞歸

a、利用存儲過程，循環(huán)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"的節(jié)點的所有上級節(jié)點：

SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));

b、利用with recursive 查詢實現(xiàn)向上遞歸

with recursive type_cte as (
    select id,name ,parent_id  from province  where id = 46
    union all
    select t.id,concat(type_cte2.name,'>',t.name),t.parent_id
    from province t
             inner join type_cte type_cte2 on t.id  = type_cte2.parent_id
)
select
    id, name, parent_id
from type_cte;

到此這篇關于mysql實現(xiàn)遞歸查詢的方法示例的文章就介紹到這了,更多相關mysql 遞歸查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

詳細聊聊MySQL中的LIMIT語句
大家應該都知道LIMIT子句可以被用于強制SELECT語句返回指定的記錄數(shù),這篇文章主要給大家介紹了關于MySQL中LIMIT語句的相關資料,需要的朋友可以參考下
2021-10-10
MySQL慢查詢日志的基本使用教程
MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。這篇文章主要給大家介紹了關于MySQL慢查詢日志的基本使用的相關資料，需要的朋友可以參考下
2019-01-01
MySQL 處理重復數(shù)據(jù)的方法(防止、刪除)
這篇文章主要介紹了MySQL 處理重復數(shù)據(jù)的方法，文中示例代碼非常詳細，幫助大家更好的理解和學習，感興趣的朋友可以了解下
2020-07-07
MySQL Group Replication(MGR)集群部署實現(xiàn)自動故障切換問題記錄
文章詳細介紹了MGR的配置步驟、環(huán)境規(guī)劃、自動故障切換驗證以及生產環(huán)境優(yōu)化和常見問題解決,感興趣的朋友跟隨小編一起看看吧
2026-01-01
MySQL中常用的一些日期和時間函數(shù)
MySQL日期和時間函數(shù)是MySQL中的一個重要組件,它提供了一些有用的函數(shù)來處理日期和時間數(shù)據(jù),這篇文章主要給大家介紹了關于MySQL中常用的一些日期和時間函數(shù),文中通過代碼介紹的非常詳細,需要的朋友可以參考下
2024-06-06
MySQL死鎖套路之唯一索引下批量插入順序不一致
這篇文章主要給大家介紹了關于MySQL死鎖套路之唯一索引下批量插入順序不一致的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
2019-06-06
MySQL9.0默認路徑安裝下重置root密碼
本文主要介紹了MySQL9.0默認路徑安裝下重置root密碼,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2025-02-02
MySQL獲取當前時間、年月與年月日實例代碼
日期和時間函數(shù)部分也是我們日常工作中使用頻率比較高的一部分,下面這篇文章主要給大家介紹了關于MySQL獲取當前時間、年月與年月日的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
2023-05-05
MySQL去除字段里數(shù)字的示例代碼
本文主要介紹了MySQL去除字段里數(shù)字的示例代碼，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-06-06
MySQ中出現(xiàn)幻讀問題的解決過程
文章解析MySQL InnoDB通過MVCC與間隙鎖機制在可重復讀隔離級別下解決幻讀,確保事務一致性,同時指出性能影響及樂觀鎖等替代方案,幫助開發(fā)者優(yōu)化數(shù)據(jù)庫應用
2025-08-08