常见数据库树形结构数据查询(查询节点所有子节点数据)
创建表
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 ))
)
) ;