MySQL 自关联详解

有时候要建立联系的两张表很类似时，可以写在一张表中，就以省、市、县为例：

mysql> create table areas(
    -> id INT PRIMARY KEY,
    -> name VARCHAR(20),
    -> pid INT,
    -> FOREIGN KEY(pid) references areas(id))character set utf8;

查看一下

desc areas;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| pid   | int(11)     | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

添加以下数据：

mysql> select * from areas;
+----+--------+------+
| id | name   | pid  |
+----+--------+------+
|  1 | 山西   | NULL |
|  2 | 陕西   | NULL |
|  3 | 太原   |    1 |
|  4 | 大同   |    1 |
|  5 | 西安   |    2 |
|  6 | 咸阳   |    2 |
|  7 | 曲阳县 |    3 |
|  8 | 天镇县 |    4 |
+----+--------+------+

这里1，2是省份 ，3，4，5，6是市，最后曲阳在太原、天镇在大同。以此为例：

mysql> select a1.name,a2.name,a3.name from areas as a1
    -> join areas as a2 on a1.pid=a2.id
    -> join areas as a3 on a2.pid=a3.id;
+--------+------+------+
| name   | name | name |
+--------+------+------+
| 曲阳县 | 太原 | 山西 |
| 天镇县 | 大同 | 山西 |
+--------+------+------+

可以在一张表中自关联直接查找，再做稍微修改``

mysql> select a1.name as county,a2.name as city,a3.name as province from areas as a1
    -> join areas as a2 on a1.pid=a2.id
    -> join areas as a3 on a2.pid=a3.id;
+--------+------+----------+
| county | city | province |
+--------+------+----------+
| 曲阳县 | 太原 | 山西     |
| 天镇县 | 大同 | 山西     |
+--------+------+----------+

把对应的县、市、省 列名修改 就?了