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=' 青岛市');