mysql 树形递归sql版及代码版

最新推荐文章于 2023-09-11 10:10:20 发布

雨夜之殇

最新推荐文章于 2023-09-11 10:10:20 发布

阅读量151

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_35484657/article/details/132043002

版权

1.数据准备

1.1 建表语句

CREATE TABLE `xu_test_tree` (
  `id` int NOT NULL COMMENT '主键',
  `parent_id` int DEFAULT NULL COMMENT '父主键',
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`) USING BTREE
)  COMMENT='测试树形结构';

1.2 表数据语句

INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (1, 0, '大类一');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (2, 0, '大类二');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (3, 0, '大类三');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (4, 0, '大类四');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (5, 1, '子类0101');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (6, 1, '子类0102');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (7, 1, '子类0103');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (8, 2, '子类0201');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (9, 2, '子类0202');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (10, 3, '子类0301');
INSERT INTO `xushu_test`.`xu_test_tree` (`id`, `parent_id`, `name`) VALUES (11, 3, '子类0302');

1.3 数据效果图

2.sql版递归查询

2.1 语句

-- 查询父级是1 的所有子集数据
with recursive t as
	 (
			 select id, parent_id, name
			 from xu_test_tree where parent_id = 1
			 union all
			 select a.id, a.parent_id, a.name
			 from xu_test_tree a join t on a.parent_id = t.id
	 )
select id, parent_id, name from t;

2.2 效果图

3.代码版

3.1 实体类字段

@Data
@NoArgsConstructor
@AllArgsConstructor
public class XxxCombinecase {
    // 主键
	private Long id;
	
    // 父主键
	private Long parentId;   
	
	//名称 
	private String name;

}

3.2 辅助方法

//递归求合并子集
public List<XxxCombinecase> CascadeList(int parentId){
	List<XxxCombinecase> list = new ArrayList<>();
	XxxCombinecase xxxCombinecase = new XxxCombinecase();
	xxxCombinecase.setParentId(num);
	List<XxxCombinecase>  selectList = xxxCombinecaseMapper.selectXxxCombinecaseList(xxxCombinecase);
	list.addAll(selectList);
	for(XxxCombinecase xxxCombinecase1 : selectList){
		list.addAll(CascadeList(xxxCombinecase1.getParentId()));
	}

	return list;
}