mysql 同表父子关系查询亲测可用 _关系

大道之行，天下为公。这篇文章主要讲述mysql 同表父子关系查询亲测可用相关的知识，希望能为你提供帮助。

一、函数系列：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>

【mysql 同表父子关系查询亲测可用】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>

二、普通Sql【mybatis】

< !--根据父类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>