根据子ID查询所有的父ID、根据父ID查询所有的子ID

最新推荐文章于 2022-12-26 17:09:29 发布

zh_39446980

最新推荐文章于 2022-12-26 17:09:29 发布

阅读量987

点赞数

分类专栏： mysql 文章标签：数据库 sql变量

本文链接：https://blog.csdn.net/weixin_39446980/article/details/114108148

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

-- 根据子ID查询所有的父ID
SELECT T2.func_id, T2.func_name ,T1.parent_id,lvl
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM bp_func WHERE func_id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 147, @l := 0) vars, 
        bp_func h 
    WHERE @r <> 0) T1 
JOIN bp_func T2 
ON T1._id = T2.func_id 
ORDER BY T1.lvl DESC;



-- 根据子ID查询所有的父ID（忽略层级）
SELECT T2.id
        FROM (
                     SELECT @r AS _id,
        (SELECT @r := parent_id FROM sys_department WHERE id = _id) AS parent_id
                     FROM
                             (SELECT @r := #{map.departmentId}) vars,
                             sys_department h
                     WHERE @r != 0) T1
                     JOIN sys_department T2
                ON T1._id = T2.id;

-- 根据父ID查询所有的子ID
SELECT c.func_id FROM
					(SELECT @id idlist, @lv := @lv + 1 lv,(SELECT @id := GROUP_CONCAT(func_id SEPARATOR ',') FROM bp_func WHERE FIND_IN_SET(parent_id, @id)) sub 
						FROM bp_func, (SELECT @id := 10, @lv := 0) vars WHERE @id IS NOT NULL
					) t, bp_func c 
				WHERE FIND_IN_SET(c.parent_id, t.idlist);



-- 根据父ID查询所有的子ID（忽略层级）
SELECT c.id
        FROM (SELECT @id idlist, (SELECT @id := GROUP_CONCAT(id SEPARATOR ',')
              FROM sys_department
              WHERE FIND_IN_SET(parent_id, @id)) sub FROM sys_department, (SELECT @id := #{id}) vars
        WHERE @id IS NOT NULL
                ) t
            , sys_department c
        WHERE FIND_IN_SET(c.parent_id, t.idlist);