【MySQL常见面试题】MySQL树结构数据查询

• 数据准备、说明
• • 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)

也就是没有父节点的节点

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

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

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)

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

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)

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)