-- 从父查子
SELECT
*
FROM
(
SELECT
rd.*
FROM
( SELECT * FROM tag_system WHERE parent_sys_id IS NOT NULL ) rd,
( SELECT @pid :=#{你要传的要查的标识} ) pd
WHERE
FIND_IN_SET( parent_sys_id, @pid ) > 0
AND @pid := concat( @pid, ',', sys_id ) UNION
SELECT
*
FROM
tag_system
WHERE
FIND_IN_SET( sys_id, @pid ) > 0
) t
ORDER BY
t.sys_id ASC
示例图:
-- 从子查父
SELECT t2.* FROM ( SELECT @r AS _id,( SELECT @r := parent_sys_id FROM tag_system WHERE sys_id = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @r := #{你要传的要查的标识} , @l := 0 ) vars,
tag_system h
WHERE
@r != 0
) T1
JOIN tag_system T2 ON T1._id = T2.sys_id
示例图:
注: 以上代码中sys_id为主标识, parent_sys_id为父级标识.
-- 顺便给大家一个组织成树形工具类
publc class TreeUtil{
public static List<TagSystem> listTree(List<TagSystem> list) {
ArrayList<TagSystem> tree = new ArrayList<>();
for (TagSystem tagSystem : list) {
//查询到根节点,从根节点组织成树形.(我这的根节点的父级标识为0)
if (tagSystem.getParentSysId() == 0) {
tree.add(findChild(tagSystem, list));
}
}
return tree;
}
public static TagSystem findChild(TagSystem tagSystem, List<TagSystem> list) {
ArrayList<TagSystem> child = new ArrayList<TagSystem>();
for (TagSystem system : list) {
if (system.getParentSysId().equals(tagSystem.getSysId())) {
child.add(findChild(system, list));
}
}
tagSystem.setChildrenList(child);
return tagSystem;
}
}
亲测有用.