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