准备表和数据
create table node_tree(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY, id
node_name varchar(128) NOT NULL DEFAULT '', 节点名字
up_node_id int, 上级结点id
node_level char(1) 节点等级
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
insert into node_tree(node_name,up_node_id,node_level)
values
('jx', NULL, '1'),
('jx.webserver', 1, '2'),
('jx.webserver.nginx1', 2, '3'),
('jx.logserver', 1, '2')
insert into node_tree(node_name,up_node_id,node_level)
values
('jx.logserver.logstash1', 4, '3')
create table node_tree1(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(128) NOT NULL DEFAULT '',
level char(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
create table node_tree2(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(128) NOT NULL DEFAULT '',
up_id int,
level char(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
insert into node_tree1(name, level)
values
('yx', '1');
insert into node_tree2(name, up_id, level)
values
('jx.webserver', 1, '2'),
('jx.logserver', 1, '2')
insert into node_tree1(name, level)
values
('xs', '1');
insert into node_tree2(name, up_id, level)
values
('xs.webserver', 2, '2');
查询jx节点的子节点
select node_tree.node_name,nt2.node_name from node_tree,node_tree as nt2 where
node_tree.node_name='jx' and node_tree.id=nt2.up_node_id
有一个表的别名是nt2，相当于将两个表连接起来，两个表之间有一定的关系
