文章目录

• 数据准备、说明
• • 1. 查询树的根节点
  • 2. 查询树的叶子节点
  • 3. 查询既不是叶子也不是根的节点
• 组合在一起查询
• case的查询方式

数据准备、说明

新建一张组织结构表，字段如下

mysql> desc t_org;+-------+--------------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int(11)      | NO   | PRI | NULL    ||| p_id  | int(11)      | YES  |     | NULL    ||| name  | varchar(255) | YES  |     | NULL    ||+-------+--------------+------+-----+---------+-------+

插入一些数据

mysql> insert into t_org (id, p_id, name) values (1,null,'一级机构'),(2,1,'二级机构1-1'),(3,1,'二级机构1-2'),(4,2,'三级机构2-1'),(5,3,'三级机构3-1');Query OK, 5 rows affected (0.03 sec)Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_org;+----+------+-------------+| id | p_id | name |+----+------+-------------+|  1 | NULL | 一级机构    ||  2 |    1 | 二级机构1-1 ||  3 |    1 | 二级机构1-2 ||  4 |    2 | 三级机构2-1 ||  5 |    3 | 三级机构3-1 |+----+------+-------------+5 rows in set (0.06 sec)

1. 查询树的根节点

也就是没有父节点的节点

mysql> select * from t_org where p_id is null;+----+------+----------+| id | p_id | name     |+----+------+----------+|  1 | NULL | 一级机构 |+----+------+----------+1 row in set (0.03 sec)

2. 查询树的叶子节点

也就是没有孩子、且有父节点的节点

mysql> select * from t_org where id not in (select distinct p_id from t_org where p_id is not null) and p_id is not null;+----+------+-------------+| id | p_id | name |+----+------+-------------+|  4 |    2 | 三级机构2-1 ||  5 |    3 | 三级机构3-1 |+----+------+-------------+2 rows in set (0.05 sec)

3. 查询既不是叶子也不是根的节点

也就是有孩子、且有父节点的节点

mysql> select * from t_org where id in (select distinct p_id from t_org where p_id is not null) and p_id is not null;+----+------+-------------+| id | p_id | name |+----+------+-------------+|  2 |    1 | 二级机构1-1 ||  3 |    1 | 二级机构1-2 |+----+------+-------------+2 rows in set (0.05 sec)

组合在一起查询

mysql> select id, name, 'root' as type from t_org where p_id is null union all select id, name, 'inner' as type from t_org where id in (select distinct p_id from t_org where p_id is not null) and p_id is not null union all select id, name, 'leaf' as type from t_org where id not in (select distinct p_id from t_org where p_id is not null) and p_id is not null;+----+-------------+-------+| id | name | type  |+----+-------------+-------+|  1 | 一级机构    | root  ||  2 | 二级机构1-1 | inner ||  3 | 二级机构1-2 | inner ||  4 | 三级机构2-1 | leaf  ||  5 | 三级机构3-1 | leaf  |+----+-------------+-------+5 rows in set (0.07 sec)

case的查询方式

mysql> select id, name, case    -> when id in (select id from t_org where p_id is null) then 'root'    -> when id in (select p_id from t_org where p_id is not null) then 'inner'    -> else 'leaf' end as 'type' from t_org;+----+-------------+-------+| id | name | type  |+----+-------------+-------+|  1 | 一级机构    | root  ||  2 | 二级机构1-1 | inner ||  3 | 二级机构1-2 | inner ||  4 | 三级机构2-1 | leaf  ||  5 | 三级机构3-1 | leaf  |+----+-------------+-------+5 rows in set (0.04 sec)