SELECT c.id ,c.parent_id FROM
(SELECT @id idlist, @lv := @lv + 1 lv,
(SELECT @id := GROUP_CONCAT(id SEPARATOR ',') FROM Emp WHERE FIND_IN_SET(parent_id, @id)) sub
FROM Emp, (SELECT @id := '3', @lv := 0) vars WHERE @id IS NOT NULL
) t, Emp c
WHERE FIND_IN_SET(c.parent_id, t.idlist)
2.代码实现
public class Tree {
private String code;
private String name;
private String parentCode;
private Integer level;
private Tree parent;
private List<Tree> node;
public Tree() {
}
public Tree(String code, String name, String parentCode, Integer level) {
this.code = code;
this.name = name;
this.parentCode = parentCode;
this.level = level;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
/**
* 将一组无序的树组装成树形结构的数据
*
* @param treeList
* @return
*/
public static List<Tree> buildTree(List<Tree> treeList) {
if (treeList == null || treeList.size() == 0) {
return null;
}
List<Tree> trees = getRootNode(treeList);
trees.forEach(node -> node.setLevel(0));
List<Tree> parentNodes = trees;
List<Tree> subNodes;
int level = 1;
do {
treeList.removeAll(parentNodes);
subNodes = getRootNode(treeList);
if (subNodes != null && subNodes.size() > 0) {
for (Tree subNode : subNodes) {
subNode.setLevel(level);
for (Tree parentNode : parentNodes) {
if (codeEquals(parentNode.getCode(), subNode.getParentCode())) {
List<Tree> list = parentNode.getNode();
if (list == null) {
list = new ArrayList<>();
parentNode.setNode(list);
}
list.add(subNode);
subNode.setParent(parentNode);
}
}
}
}
parentNodes = subNodes;
level++;
} while (subNodes != null);
return trees;
}
/**
* 获取treeList中的顶级节点
*
* @param treeList
* @return
*/
public static List<Tree> getRootNode(List<Tree> treeList) {
if (treeList == null || treeList.size() == 0) {
return null;
}
List<String> codes = treeList.stream().map(tree -> tree.getCode()).collect(Collectors.toList());
List<Tree> trees = treeList.stream().filter(
node -> !codes.contains(node.getParentCode()) || codeEquals(node.getCode(), node.getParentCode()))
.collect(Collectors.toList());
return trees;
}
public static void printTree(List<Tree> trees){
for(Tree tree : trees){
String prefix = "----";
for(int i=0;tree.getLevel()!=null &&i<tree.getLevel();i++){
prefix += "----";
}
System.out.println(prefix+tree);
if(tree.getNode() != null && tree.getNode().size() > 0){
printTree(tree.getNode());
}
}
}
/**
* 判断两个编码是否相同
*
* @param code
* @param parentCode
* @return
*/
public static boolean codeEquals(String code, String parentCode) {
if (code == null) {
if (parentCode == null) {
return true;
}
} else {
if (parentCode != null && code.equals(parentCode)) {
return true;
}
}
return false;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getParentCode() {
return parentCode;
}
public void setParentCode(String parentCode) {
this.parentCode = parentCode;
}
public Integer getLevel() {
return level;
}
public void setLevel(Integer level) {
this.level = level;
}
public Tree getParent() {
return parent;
}
public void setParent(Tree parent) {
this.parent = parent;
}
public List<Tree> getNode() {
return node;
}
public void setNode(List<Tree> node) {
this.node = node;
}
@Override
public String toString() {
return "Tree{" + "code='" + code + '\'' + ", name='" + name + '\'' + ", parentCode='" + parentCode + '\''
+ ", level=" + level + '}';
}
}
测试类
public class TreeTest {
public static void main(String[] args) {
Tree tree0 = new Tree(null,"根节点0",null,null);
Tree tree1 = new Tree("1","根节点1",null,null);
Tree tree2 = new Tree("2","根节点2",null,null);
Tree tree3 = new Tree("3","子节点-2-3","2",null);
Tree tree4 = new Tree("4","子节点-2-3","2",null);
Tree tree5 = new Tree("5","根节点0-5","0",null);
Tree tree6 = new Tree("6","根节点0-6","0",null);
Tree tree7 = new Tree("7","根节点0-6-7","6",null);
Tree tree8 = new Tree("8","根节点0-6-7-8","7",null);
Tree tree9 = new Tree("9","根节点0-5-9","5",null);
Tree tree10 = new Tree("10","根节点0-5-10","5",null);
Tree tree13 = new Tree("13","根节点0-5-10-13","10",null);
Tree tree11 = new Tree("11","根节点0-5-10-13-11","13",null);
Tree tree12 = new Tree("12","根节点0-5-10-13-12","13",null);
List<Tree> treeList = new ArrayList<>();
treeList.add(tree0);
treeList.add(tree1);
treeList.add(tree2);
treeList.add(tree3);
treeList.add(tree4);
treeList.add(tree5);
treeList.add(tree6);
treeList.add(tree7);
treeList.add(tree8);
treeList.add(tree9);
treeList.add(tree10);
treeList.add(tree11);
treeList.add(tree12);
treeList.add(tree13);
List<Tree> trees = Tree.buildTree(treeList);
printTree(trees);
}
private static void printTree(List<Tree> trees){
for(Tree tree : trees){
String prefix = "----";
for(int i=0;i<tree.getLevel();i++){
prefix += "----";
}
System.out.println(prefix+tree);
if(tree.getNode() != null && tree.getNode().size() > 0){
printTree(tree.getNode());
}
}
}