常见数据库树形结构数据查询(查询节点所有子节点数据)

最新推荐文章于 2023-06-07 22:53:13 发布

wwh_a123

最新推荐文章于 2023-06-07 22:53:13 发布

阅读量774

点赞数

分类专栏：数据库文章标签：数据库 database

本文链接：https://blog.csdn.net/wwh_a123/article/details/121723835

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

常见数据库树形结构数据查询(查询节点所有子节点数据)

创建表


DROP TABLE IF EXISTS `experiment_folder`;
CREATE TABLE `experiment_folder` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(128) NOT NULL COMMENT '文件夹名称',
  `grade_id` int(11) NOT NULL COMMENT '文件夹级别',
  `parent_id` bigint(20) NOT NULL COMMENT '父级id',
  `user_id` int(11) NOT NULL COMMENT '用户id',
  `status` tinyint(4) NOT NULL COMMENT '文件状态(0为开启 ; 1为关闭)',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `project_id` int(11) NOT NULL COMMENT '项目id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of experiment_folder
-- ----------------------------
INSERT INTO `experiment_folder` VALUES ('24', 'A单位', '0', '0', '4', '0', '2021-03-05 00:25:17', '2021-03-05 00:25:17', '1');
INSERT INTO `experiment_folder` VALUES ('25', 'A单位-20210305-002518', '0', '0', '4', '0', '2021-03-05 00:25:19', '2021-03-05 00:25:19', '1');
INSERT INTO `experiment_folder` VALUES ('26', 'A单位-20210305-002519', '0', '0', '4', '0', '2021-03-05 00:25:20', '2021-03-05 00:25:20', '1');
INSERT INTO `experiment_folder` VALUES ('27', 'B单位', '0', '0', '4', '0', '2021-03-05 00:26:17', '2021-03-05 00:26:17', '1');
INSERT INTO `experiment_folder` VALUES ('28', 'B单位-20210305-002617', '0', '0', '4', '0', '2021-03-05 00:26:17', '2021-03-05 00:26:17', '1');
INSERT INTO `experiment_folder` VALUES ('29', 'B单位-20210305-002618', '0', '0', '4', '0', '2021-03-05 00:26:18', '2021-03-05 00:26:18', '1');
INSERT INTO `experiment_folder` VALUES ('30', 'A单位ziji', '1', '24', '4', '0', '2021-03-05 00:29:18', '2021-03-05 00:29:18', '1');
INSERT INTO `experiment_folder` VALUES ('31', 'A单位ziji', '1', '25', '4', '0', '2021-03-05 00:29:20', '2021-03-05 00:29:20', '1');
INSERT INTO `experiment_folder` VALUES ('32', 'A单位ziji', '1', '26', '4', '0', '2021-03-05 00:29:23', '2021-03-05 00:29:23', '1');
INSERT INTO `experiment_folder` VALUES ('33', 'B单位', '1', '27', '4', '0', '2021-03-05 00:29:53', '2021-03-05 00:29:53', '1');
INSERT INTO `experiment_folder` VALUES ('34', 'B单位', '1', '28', '4', '0', '2021-03-05 00:29:55', '2021-03-05 00:29:55', '1');
INSERT INTO `experiment_folder` VALUES ('35', 'B单位', '1', '29', '4', '0', '2021-03-05 00:29:57', '2021-03-05 00:29:57', '1');
INSERT INTO `experiment_folder` VALUES ('36', 'A单位-下一级', '1', '24', '4', '0', '2021-03-05 11:56:09', '2021-03-05 11:56:09', '1');
INSERT INTO `experiment_folder` VALUES ('37', '你明明', '1', '24', '4', '0', '2021-03-05 11:56:11', '2021-06-11 16:45:01', '1');
INSERT INTO `experiment_folder` VALUES ('38', 'A单位-下一级-20210305-115619', '1', '24', '4', '0', '2021-03-05 11:56:19', '2021-03-05 11:56:19', '1');
INSERT INTO `experiment_folder` VALUES ('39', '公民', '0', '0', '1', '0', '2021-06-04 13:52:49', '2021-06-10 19:23:39', '1');
INSERT INTO `experiment_folder` VALUES ('46', 'test文件夹-20210609-160938', '0', '0', '1', '0', '2021-06-09 16:09:38', '2021-06-09 16:09:38', '1');
INSERT INTO `experiment_folder` VALUES ('51', '明明噢你', '0', '0', '1', '0', '2021-06-10 19:32:42', '2021-06-11 23:42:00', '1');

多级查询

SELECT
	*
FROM
	experiment_folder
WHERE
	id IN (
		SELECT
			id
		FROM
			experiment_folder
		WHERE
			pid in (1,6)
		UNION
			(
				SELECT
					id
				FROM
					(
						SELECT
							id
						FROM
							experiment_folder
						ORDER BY
							pid,
							id
					) experiment_folder_sort,
					(SELECT @pv in (1,6)) init_experiment_folder
				WHERE
					FIND_IN_SET(pid ,@pv)
				AND LENGTH(@pv := concat(@pv, ',', id))
			)
	);

一级查询

SELECT
	*
FROM
	experiment_folder
WHERE
	id IN (
		SELECT
			id
		FROM
			experiment_folder
		WHERE
			pid = 1
		UNION
			(
				SELECT
					id
				FROM
					(
						SELECT
							id
						FROM
							experiment_folder
						ORDER BY
							pid,
							id
					) experiment_folder_sort,
					(SELECT @pv := 1) init_experiment_folder
				WHERE
					FIND_IN_SET(pid ,@pv)
				AND LENGTH(@pv := concat(@pv, ',', id))
			)
	);