MySQL根据父节点id查询所有子节点
方法一
<!--根据id查询子节点--> <select id="sonId" parameterType="java.lang.String" resultType="com.tl.visual.model.SystemRelate"> SELECT class_id FROM ( SELECT t1.class_id, IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', class_id), 0) AS ischild FROM ( SELECT class_id,parent_id FROM 表名 t WHERE t.state = '01' ORDER BY parent_id, class_id ) t1, (SELECT @pids := #{要查询的id}) t2 ) t3 WHERE ischild != 0 </select>
方法二
SELECT * FROM ( SELECT t1.*, IF(FIND_IN_SET(pid, @pids) > 0, @pids := CONCAT(@pids, ',', id), '0') AS ischild FROM ( SELECT * FROM test2 AS t WHERE t.state = '01' ORDER BY t.id ASC ) t1, (SELECT @pids := 'b1') t2 ) t3 WHERE ischild != '0'
替换的地方:
test2 替换成你的表名
pid 替换成你的父id
id 替换成你的主键id
state 组织状态
b1 替换成你要查询的主键id
每天学习一点点,你就进步一点点。