准备表和数据

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，相当于将两个表连接起来，两个表之间有一定的关系