1、根据传入id查询所有父节点的id
delimiter //
CREATE FUNCTION `getParList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =rootId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个，不加的话第一个会为空
IF sTemp != '' THEN
SET sTemp = concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(pid【父节点ID】) INTO sTempPar FROM 表名 where pid<>id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
//
普通查询： select * from treenodes where FIND_IN_SET(id,getParList(15));
mybatis：
<!--根据子id查找其父类别-->
<select id="getParList" resultMap="BaseResultMap" parameterType="Integer">
select
c.id,c.ad_name,c.ad_parent_code
from
<include refid="tableName"></include> c
where
FIND_IN_SET(id,getADParList(#{id}));
</select>
2、根据传入id查询所有子节点的id
delimiter //
CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM 表名 where FIND_IN_SET(父节点ID,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
//执行命令
普通查询：select * from treenodes where FIND_IN_SET(id,getChildList(7));
mybatis：
<!--根据父类id查找其子类别-->
<select id="getChildList" resultMap="BaseResultMap" parameterType="Integer">
select
c.id,c.ad_name,c.ad_parent_code
from
<include refid="tableName"></include> c
where
FIND_IN_SET(id,getADChildList(#{id}));
</select>
<!--根据父类id查找其子类别-->
<select id="getById" resultMap="BaseResultMap" parameterType="Integer">
SELECT
<include refid="Base_Column_List" />
FROM
<include refid="tableName"></include>
WHERE
parent_id = #{id}
</select>
<!--查找所有类别(递归)-->
<select id="getAll" resultMap="BaseResultMap" parameterType="Integer">
SELECT
<include refid="Base_Column_List" />
FROM
<include refid="tableName"></include>
WHERE 1 = 1
<choose>
<when test="ad_parent_code ==0">
AND c_bi_admin_division.ad_parent_code IS NULL
</when>
<otherwise>
AND id = #{ad_parent_code}
</otherwise>
</choose>
</select>
1、普通sql查询：
SELECT T2.id,T2.ad_name[ps:字段]
FROM (
SELECT
@r AS _id,
(SELECT @r := ad_parent_code[ps：父id] FROM 表名 WHERE id = _id) AS ad_parent_code,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{id}, @l := 0) vars,
表名 h
WHERE @r <> 0) T1
JOIN 表名 T2
ON T1._id = T2.id
ORDER BY id;
举个栗子：
SELECT T2.id,T2.uname
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM user WHERE id = _id) AS parent_id ,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{id}, @l := 0) vars,
user h
WHERE @r <> 0) T1
JOIN user T2
ON T1._id = T2.id
ORDER BY id;
2、mybatis【mapper.xml使用】
<select id="findParentMessageById" parameterType="Integer" resultMap="BaseResultMap">
<![CDATA[
SELECT T2.id,T2.uname
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM user WHERE id = _id) AS parent_id ,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{id}, @l := 0) vars,
user h
WHERE @r <> 0) T1
JOIN user T2
ON T1._id = T2.id
ORDER BY id;
]]>
</select>
想要实时关注更多干货好文，扫描下图关注：
