表结构
CREATE TABLE `sys_permission` (
`fn_code` varchar(50) NOT NULL COMMENT '功能权限id 功能权限编码',
`fn_name` varchar(100) DEFAULT NULL COMMENT '功能权限名',
`parent_id` varchar(11) DEFAULT NULL COMMENT '上级功能权限'
PRIMARY KEY (`fn_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='权限表';
子求父
5.7版本
<foreach collection="permissionList" separator=" union " item="fnCode" index="i">
SELECT t2.fn_code, t2.`fn_name`,t2.fn_order
FROM
(
SELECT
@r${i} AS _id,
(SELECT @r${i} := parent_id FROM sys_permission WHERE fn_code = _id) AS parent_id,
@l${i} := @l${i} + 1 AS lvl
FROM
(SELECT @r${i} := #{fnCode}, @l${i} := 0) vars, sys_permission AS h
WHERE @r${i} != 0
) t1
JOIN sys_permission t2
ON t1._id = t2.fn_code
</foreach>
原始
SELECT
t2.fn_code,
t2.`fn_name`,
t2.fn_order
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM sys_permission WHERE fn_code = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @r := '010101', @l := 0 ) vars,
sys_permission AS h
WHERE
@r <> 0
) t1
JOIN sys_permission t2 ON t1._id = t2.fn_code
8.0版本
WITH recursive temp AS (
SELECT
*
FROM
sys_permission p
WHERE
p.fn_code in
<foreach collection="permissionList" open="(" close=")" item="fnCode" separator=",">
#{fnCode}
</foreach>
UNION ALL
SELECT
*
FROM
sys_permission p,
temp t
WHERE
t.parent_id = p.fn_code
)
SELECT
*
FROM
temp
父获子
8.0版本
WITH recursive temp AS (
SELECT
*
FROM
sys_permission p
WHERE
p.fn_code in
<foreach collection="permissionList" open="(" close=")" item="fnCode" separator=",">
#{fnCode}
</foreach>
UNION ALL
SELECT
*
FROM
sys_permission p,
temp t
WHERE
t.fn_code = p.parent_id
)
SELECT
*
FROM
temp
5.7
根据一个父节点查询所有子节点(包含自身)
SELECT
au.fn_code
FROM
( SELECT * FROM sys_permission WHERE parent_id IS NOT NULL ) au,
( SELECT @pid := '01' ) pd
WHERE
FIND_IN_SET( parent_id, @pid ) > 0
AND @pid := concat( @pid, ',', fn_code ) UNION
SELECT
fn_code
FROM
sys_permission
WHERE
fn_code = '01';
根据多个父节点查询所有子节点(包含自身)
SELECT
au.fn_code
FROM
( SELECT * FROM sys_permission WHERE parent_id IS NOT NULL ) au,
( SELECT @pid := '01,02' ) pd
WHERE
FIND_IN_SET( parent_id, @pid ) > 0
AND @pid := concat( @pid, ',', fn_code ) UNION
SELECT
fn_code
FROM
sys_permission
WHERE
FIND_IN_SET( fn_code, @pid ) > 0;