mysql 树形查询，根据子id查询所有父id/父名称，根据父id查询子信息

逃离我推掉我的手 2023-10-14 18:39 48阅读 0赞

表名：dw\_region 把查询语句的\#\{id\} 替换 子id![c2ba1d3ee0ef4e2c90d57c55762502e6.png][]

查询父id

select parent_ids from (
            select
            @id as  id ,
            (
            SELECT
            @id :=max(parent_id)
            FROM
            dw_region a
            WHERE
            a.id=@id
            ) AS parent_ids,
            @l := @l + 1 AS `level`
            FROM
            dw_region dw_region,
            (SELECT @id := (#{id}), @l := 0) b
            )a where parent_ids is not null  order by `level` desc

查询父name

SELECT  GROUP_CONCAT(`name` ORDER BY id ) regionName FROM dw_region WHERE id in(
            select parent_ids from (
                                       select
                                           @id as  id ,
                                           (
                                               SELECT
                                                   @id :=max(parent_id)
                                       FROM
                                           dw_region a
                                       WHERE
                                           a.id=@id
                                   ) AS parent_ids,
                @l := @l + 1 AS `level`
            FROM
                dw_region dw_region,
                (SELECT @id := (#{id}), @l := 0) b
                )a where parent_ids is not null )

根据id查询地区，has\_children （Boolean）返回是否还有下级（儿子），parentIds 所有的父id（集合）

![a365428672c347e895f2b7c6055a0703.png][]

![a62dd73ce17a4bc1a7f520d9a06cbb26.png][]

SELECT
    	id,
    	parent_id,
    	`name`,
    	address_code
    CASE
    	
    	WHEN has_children > 0 THEN
    	has_children ELSE 0 
    	END has_children 
    FROM
    	dw_region dw_region
    	LEFT JOIN ( SELECT count( 0 ) AS has_children, parent_id AS child_id FROM dw_region GROUP BY parent_id ) dw_region2 ON dw_region2.child_id = dw_region.id 
    WHERE
    dw_region.id = #{id}

返回格式：has\_children （Boolean）返回是否还有下级（儿子）

parentIds: 所有的父id（集合）

![25503e421cb84d97878fc12c712ddcb5.png][]

根据父id查询子所有信息

SELECT
                *
            FROM
                (
                    SELECT
                        t1.*,
                        IF
                            ( FIND_IN_SET( parent_id, @pids ) > 0, @pids := CONCAT( @pids, ',', id ), '0' ) AS ischild
                    FROM
                            ( SELECT * FROM  你的数据表 AS t  ORDER BY t.id ASC ) t1,
                            ( SELECT @pids := #{你的父id}) t2
                ) t3
            WHERE
                ischild != '0'
    
    //我的数据表  是id和parent_id  ，如果你的不是parent_id，也替换一下

[c2ba1d3ee0ef4e2c90d57c55762502e6.png]: https://img-blog.csdnimg.cn/c2ba1d3ee0ef4e2c90d57c55762502e6.png
[a365428672c347e895f2b7c6055a0703.png]: https://img-blog.csdnimg.cn/a365428672c347e895f2b7c6055a0703.png
[a62dd73ce17a4bc1a7f520d9a06cbb26.png]: https://img-blog.csdnimg.cn/a62dd73ce17a4bc1a7f520d9a06cbb26.png
[25503e421cb84d97878fc12c712ddcb5.png]: https://img-blog.csdnimg.cn/25503e421cb84d97878fc12c712ddcb5.png