表结构
SELECT
ID.LEVEL1,
DATA1.*
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( pid ) FROM project_grouping WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
@l := @l + 1 AS LEVEL1
FROM
project_grouping,
( SELECT @ids := 1, @l := 0 ) b
WHERE
@ids IS NOT NULL
) ID,
project_grouping DATA1
WHERE
FIND_IN_SET( DATA1.pid, ID._ids )
ORDER BY
LEVEL1,
pid
不太理解网上为什么用LEVEL当作是别名 明明是关键字
@ids 为父节点id
由于业务中经常使用tree来返回而不是list<对象的形式>,现在java中进行递归造json tree
@Override
public Object selectGrouping() {
List<Project_grouping> list=Project_groupingMapper.selectTreeByParent_id(0,null);
return buildTree(list,0);
}
public static List<TreeGroup> buildTree(List<Project_grouping> list,int parent_id){
List<TreeGroup> trees =new ArrayList<TreeGroup>();
for (Project_grouping group : list) {
int id = group.getGid();//主键id
int pid = group.getParent_id();//父类id
String name = group.getName();//名称
if (parent_id == pid) {
List<TreeGroup> treeLists = buildTree(list, id);
TreeGroup tree = new TreeGroup(id, parent_id, name, treeLists);
trees.add(tree);
}
}
return trees;
}
private Integer id;
private Integer parent_id;
private String name;
private List<TreeGroup> children;
public TreeGroup(Integer id, Integer parent_id, String name, List<TreeGroup> children) {
super();
this.id = id;
this.parent_id = parent_id;
this.name = name;
this.children = children;
}