MySQL 查询所有父级或查询所有子级

最新推荐文章于 2024-09-27 09:59:22 发布

Hou-Li

最新推荐文章于 2024-09-27 09:59:22 发布

阅读量1.1k

点赞数

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/weixin_43018064/article/details/118993794

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询所有父级

SELECT *
	FROM ( 
        SELECT 
                @r AS _id, 
                (SELECT @r := parent_id FROM department WHERE id = _id) AS parent_id, 
                @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := #{id}, @l := 0) vars,
                department h 
        WHERE @r <> 0) T1 
JOIN department T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC

查询所有子级

SELECT
			*
		FROM
			(
			SELECT
				@ids AS _ids,
				( SELECT @ids := GROUP_CONCAT( id ) FROM department WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
				@l := @l + 1 AS LEVEL1
			FROM
				department,
				( SELECT @ids := #{id}, @l := 0 ) b
			WHERE
				@ids IS NOT NULL
			) id,
			department DATA
		WHERE
			FIND_IN_SET( DATA.id, _ids )
		ORDER BY
			LEVEL1