MySQL8.0和MySQL5.7的迭代查询

最新推荐文章于 2024-09-18 09:25:49 发布

原创最新推荐文章于 2024-09-18 09:25:49 发布 · 2.4k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sql

mysql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何在MySQL8.0和MySQL5.7中实现上级和下级数据的迭代查询，包括递归查询所有上级及下级节点的方法，并展示了如何构建全路径。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL8.0和MySQL5.7的迭代查询

查询上级
- MySQL8.0迭代查询所有上级（包含本身）
- MySQL5.7迭代查询所有上级（包含本身）
查询下级
- MySQL8.0迭代查询所有下级（包含本身）
- MySQL5.7迭代查询所有下级（包含本身）

MySQL5.7是不支持 WITH AS 子句的，只有MySQL 8.0 以上版本才支持。

查询上级

MySQL8.0迭代查询所有上级（包含本身）

	with recursive region_temp as (
    select *  from base_region_info  where region_id = '110101001'
    union all
    select t.*
    from base_region_info t
             inner join region_temp region_temp2 on t.region_id = region_temp2.parent_id
						 where t.region_id <> 0
)
select * from region_temp;

包含全路径

	with recursive region_temp as (
    select *,region_name all_path  from base_region_info   where region_id = '110101001'
    union all
    select t.*,concat(region_temp2.all_path,'>',t.region_name) all_path 
    from base_region_info t
             inner join region_temp region_temp2 on t.region_id = region_temp2.parent_id
						 where t.region_id <> 0
)
select * from region_temp;

MySQL5.7迭代查询所有上级（包含本身）

SELECT t2.*
FROM ( 
    SELECT  @id as _id, 
        (SELECT @id := parent_id FROM base_region_info WHERE region_id = _id) p
    FROM 
        (SELECT @id := "110101001") v, 
        base_region_info 
    WHERE @id <> "0"
		) t1 
JOIN base_region_info t2 ON t1._id = t2.region_id;

查询下级

MySQL8.0迭代查询所有下级（包含本身）

	with recursive region_temp as (
    select *  from base_region_info  where region_id = '110101001'
    union all
    select t.* from base_region_info t
                        inner join region_temp region_temp2 on t.parent_id = region_temp2.region_id
)
select
    *
from region_temp

包含全路径

	
	with recursive region_temp as (
    select *,region_name all_path  from base_region_info  where region_id = '110101001'
    union all
    select t.*,concat(region_temp2.all_path,'>',t.region_name) all_path 
		 from base_region_info t
                        inner join region_temp region_temp2 on t.parent_id = region_temp2.region_id
)
select
    *
from region_temp

MySQL5.7迭代查询所有下级（包含本身）

SELECT T2.*
FROM
	(
	SELECT
		@ids AS _ids,
		(SELECT @ids := GROUP_CONCAT(region_id) FROM base_region_info WHERE FIND_IN_SET(parent_id,@ids)) AS cids 
	FROM
		base_region_info ,
		( SELECT @ids := '110101001' ) b 
	WHERE
		@ids <> '0' 
	) T1 ,
	base_region_info T2
WHERE
	FIND_IN_SET(T2.region_id,T1 ._ids )