mysql 自关联的子孙查询_mysql 自关联、子查询

一个表中的一列，用到了自己表中的另一列，叫做自关联,可用在省市县、行政级别等。

+----+-----------+------+

| id | name| p_id |

+----+-----------+------+

|1 | 北京市| NULL |

|2 | 山东| NULL |

|3 | 济南|2 |

|4 | 青岛|2 |

|5 | 黄岛|4 |

+----+-----------+------+

创建一个表province：

create table province(id int primary key, atitle varchar(20), pid int);

导入一个sql文件：

打开终端，切换到.sql文件所在目录，进入mysql，运行:

source province.sql    #province是一个包含省市县的表 内容如下：

INSERT INTO `province` VALUES ('82', '山东省', '0');

insert into province(296,"青岛市", 82)

.....

先查一下山东的id:

select * from province where atitle="山东省";

看到山东省的id是96

+----+-----------+------+

| id | atitle| pid|

+----+-----------+------+

| 96 | 山东省| NULL |

+----+-----------+------+

查询pid为96的城市：

select * from province where pid=96;

得到山东所有的城市：

+-----+-----------+------+

| id| atitle| pid|

+-----+-----------+------+

| 295 | 济南市| 96 |

| 296 | 青岛市| 96 |

| 297 | 淄博市| 96 |

| 298 | 枣庄市| 96 |

| 299 | 东营市| 96 |

| 300 | 潍坊市| 96 |

| 301 | 烟台市| 96 |

青岛的id为296，查询pid为296的地区：

得到青岛所有的区

上面是先查省id，再根据省id查相对应的pid。

可以一步查询：

1、select * from province as pro inner join province as city on city.pid=pro.id having pro.atitle="山东省";

结果如下：

+----+-----------+------+-----+-----------+------+

| id | atitle| pid| id| atitle| pid|

+----+-----------+------+-----+-----------+------+

| 96 | 山东省| NULL | 295 | 济南市| 96 |

| 96 | 山东省| NULL | 296 | 青岛市| 96 |

| 96 | 山东省| NULL | 297 | 淄博市| 96 |

| 96 | 山东省| NULL | 298 | 枣庄市| 96 |

| 96 | 山东省| NULL | 299 | 东营市| 96 |

| 96 | 山东省| NULL | 300 | 潍坊市| 96 |

| 96 | 山东省| NULL | 301 | 烟台市| 96 |

选择想要查看的列：

select province.atitle,city.atitle from province inner join province as city on city.pid=province.id having province.atitle='山东省';

结果如下：

+-----------+-----------+

| atitle| atitle|

+-----------+-----------+

| 山东省| 济南市|

| 山东省| 青岛市|

| 山东省| 淄博市|

| 山东省| 枣庄市|

| 山东省| 东营市|

select province.atitle,city.atitle from province inner join province as city on city.pid=province.id having province.atitle='山东省';

只要替换里面的“山东省”,就可以查看输入目标id 对应的pid

注意⚠️：

1、select province.atitle,city.atitle from province inner join province as city on city.pid=province.id having province.atitle='山东省';

2、select province.atitle,province.id,city.atitle from province inner join province as city on city.pid=province.id having province.id=96;

上面两句都可以查询出结果，但是要注意：

having过滤时，用的是什么，select后面就需要至少有一个什么，或者直接是*

比如第二句,having 后面是用的provice.id=96,那么select后面就要加一个province.id，不然就报错

同理，第一句having后面用的是province.atitle，那么select后面就至少有一个province.atitle,或者直接用一个*

子查询：

查询身高最高的学生，后面的select先执行，执行结果给第一个select,后面的括号不能少：

select * from students where height = (select max(height) from students);

开始自查询的例子，也可以用子查询完成：

select * from province where pid=(select id from province where atitle=' 青岛市');