SELECT
T2.id
FROM
(
SELECT
@r AS _id,
( SELECT @r := parent_id FROM t_material_library WHERE id = _id ) AS parent_id,
@l := @l + 1 AS lvl
FROM
( SELECT @r := 94, @l := 0 ) vars,
t_material_library h
WHERE
@r <> 0
) T1
JOIN t_material_library T2 ON T1._id = T2.id
ORDER BY
T1.lvl DESC
下面是表结构:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
– Table structure for t_node_library
DROP TABLE IF EXISTS t_node_library
;
CREATE TABLE t_node_library
(
id
int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
parent_id
int(10) UNSIGNED NULL DEFAULT NULL COMMENT ‘父节点ID’,
name
varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘名称’,
type
tinyint(4) NULL DEFAULT NULL COMMENT ‘预留字段’,
leaf
tinyint(4) NULL DEFAULT NULL COMMENT ‘是否是叶子节点,0 - 否, 1 - 是’,
enabled
tinyint(4) NULL DEFAULT 1 COMMENT ‘是否启用, 0 - 禁用, 1 - 启用’,
deleted
varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT ‘0’ COMMENT ‘是否删除 0 否 1是’,
PRIMARY KEY (id
) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 141 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = ‘材料库’ ROW_FORMAT = Compact;
– Records of t_node_library
INSERT INTO t_node_library
VALUES (10, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (11, 0, ‘’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (12, 0, ‘’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (13, 11, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (14, 11, ‘222’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (15, 11, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (16, 12, ‘222’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (17, 11, ‘aaa’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (18, 0, ‘’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (19, 18, ‘1111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (20, 14, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (21, 14, ‘111111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (22, 14, ‘111111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (23, 14, ‘11111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (24, 11, ‘1111’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (25, 14, ‘111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (26, 14, ‘111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (27, 14, ‘111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (28, 25, ‘333’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (29, 26, ‘444’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (30, 27, ‘5555’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (31, 11, ‘111’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (32, 24, ‘111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (33, 24, ‘1111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (34, 33, ‘1111’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (35, 34, ‘22222’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (36, 35, ‘2222’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (37, 31, ‘111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (38, 0, ‘1111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (39, 0, ‘111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (40, 36, ‘11111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (41, 0, ‘11111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (42, 33, ‘1111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (43, 33, ‘2222’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (44, 33, ‘333’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (45, 44, ‘’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (46, 44, ‘aaaa’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (47, 37, ‘1111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (48, 0, ‘11111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (49, 0, ‘1111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (50, 0, ‘1111’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (51, 11, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (52, 0, ‘2’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (53, 0, ‘3’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (54, 0, ‘4’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (55, 0, ‘5’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (56, 0, ‘6’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (57, 51, ‘2’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (58, 57, ‘3’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (59, 0, ‘4’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (60, 0, ‘5’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (61, 11, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (62, 0, ‘2’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (63, 0, ‘3’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (64, 0, ‘4’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (65, 0, ‘5’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (66, 61, ‘2’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (67, 61, ‘1’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (68, 11, ‘1’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (69, 68, ‘2’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (70, 69, ‘3’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (71, 70, ‘4’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (72, 71, ‘5’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (73, 71, ‘5’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (74, 71, ‘6’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (75, 69, ‘3’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (76, 75, ‘6’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (77, 76, ‘5’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (78, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (79, 11, ‘11’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (80, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (81, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (82, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (83, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (84, 0, ‘’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (85, 0, ‘’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (86, 85, ‘1111’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (87, 86, ‘1111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (88, 84, ‘室内’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (89, 84, ‘1111’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (90, 89, ‘1111’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (91, 88, ‘1111’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (92, 91, ‘bbb’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (93, 0, ‘’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (94, 93, ‘有调压’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (95, 93, ‘无调压’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (96, 93, ‘其他’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (97, 96, ‘啊啊’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (98, 96, ‘宝宝’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (99, 92, ‘222’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (100, 0, ‘’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (101, 84, ‘室内材料-管道挂表管径新’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (102, 84, ‘室外材料应用’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (103, 101, ‘管道’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (104, 101, ‘管径管径’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (105, 103, ‘子管道’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (106, 105, ‘小管道小管道小管道小管道’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (107, 106, ‘三角阀三角阀三角阀’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (108, 104, ‘管道’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (109, 108, ‘设备’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (110, 109, ‘1级设备’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (111, 102, ‘外部用户’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (112, 0, ‘’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (113, 103, ‘子管道’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (114, 103, ‘完成’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (115, 114, ‘完成’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (116, 97, ‘哦哦’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (117, 84, ‘啊啊啊啊啊啊啊啊啊11’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (118, 103, ‘11’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (119, 103, ‘管首外训用户室外材料’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (120, 100, ‘有调压’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (121, 100, ‘无调压’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (122, 103, ‘子级’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (123, 103, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (124, 123, ‘2’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (125, 103, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (126, 125, ‘2’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (127, 103, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (128, 127, ‘2’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (129, 103, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (130, 129, ‘2’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (131, 103, ‘3’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (132, 101, ‘了级’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (133, 132, ‘完成级’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (134, 94, ‘子级’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (135, 102, ‘1’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (136, 135, ‘1’, NULL, 1, 1, ‘0’);
INSERT INTO t_node_library
VALUES (137, 85, ‘1’, NULL, 1, 1, ‘1’);
INSERT INTO t_node_library
VALUES (138, 94, ‘第二级’, NULL, 0, 1, ‘1’);
INSERT INTO t_node_library
VALUES (139, 138, ‘第三级’, NULL, 0, 1, ‘0’);
INSERT INTO t_node_library
VALUES (140, 139, ‘第四级’, NULL, 1, 1, ‘0’);
SET FOREIGN_KEY_CHECKS = 1;