Mysql自连接查询实例详解

Mysql自连接查询是关于一个表自己与自己进行连接查询的方法。这种查询在复杂的数据结构中非常有用，它可以让我们找到相对于自己某些列存在相似性的记录，构造示例如下：

准备工作

示例中我们使用的是employees这张表，表中存放的是雇员的信息，包括雇员编号、名字、性别、工资、职位、上司等。

CREATE TABLE employees (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM('M','F')   NOT NULL, 
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
 );

表中也存在上司-下属之间的关系，也就是同一张表中有父子级别关系，可以使用emp_no来描述两者之间的关系。

自连接查询示例

示例1：查询下属与上司的所有信息

我们可以通过自连接查询，查询一个上司及其下属的所有信息。例如查询员工编号为10001的上司及其下属的全部信息代码如下：

SELECT e1.emp_no, e1.first_name, e1.last_name, e2.emp_no, e2.first_name, e2.last_name
FROM employees AS e1, employees AS e2
WHERE e1.emp_no = e2.emp_no OR e1.emp_no = e2.emp_no;

查询结果如下表所示：

e1.emp_no	e1.first_name	e1.last_name	e2.emp_no	e2.first_name	e2.last_name
4	Margaret	Peacock	4	Margaret	Peacock
4	Margaret	Peacock	78	James	Kramer
4	Margaret	Peacock	119	Yoshinari	DasSarma
4	Margaret	Peacock	146	Berhard	Lenart
4	Margaret	Peacock	199	Vishv	Chen
4	Margaret	Peacock	218	Themistoklis	Raman
5	Wolfgang	Schlereth	5	Wolfgang	Schlereth
5	Wolfgang	Schlereth	62	Parviz	Lortz
5	Wolfgang	Schlereth	81	Xiaohua	Lu
5	Wolfgang	Schlereth	103	Shawna	Frumkin
5	Wolfgang	Schlereth	112	Wenbo	Yen
5	Wolfgang	Schlereth	117	Jussi	Kivivuori
5	Wolfgang	Schlereth	142	Hee	Karvonen
5	Wolfgang	Schlereth	168	Bangqing	Kleiser
5	Wolfgang	Schlereth	198	Gerard	Barzin
5	Wolfgang	Schlereth	242	Mariano	Erde

上述查询的限制条件是e1.emp_no = e2.emp_no OR e1.emp_no = e2.emp_no，表示自身与自身的查询结果。使用 WHERE e1.emp_no = 10001，表示要查询的员工编号为10001和他的下属，最终的查询结果即为10001这个雇员及其下属所有信息的集合。

示例2：查询有相同上司的雇员信息

查询有相同上司的雇员信息，可以使用自身表连接的方法，具体查询代码如下所示：

SELECT e1.emp_no, e1.first_name, e1.last_name, e2.emp_no, e2.first_name, e2.last_name 
FROM employees AS e1, employees AS e2 
WHERE e1.emp_no != e2.emp_no AND e1.emp_no = e2.emp_no;

查询结果如下表所示：

e1.emp_no	e1.first_name	e1.last_name	e2.emp_no	e2.first_name	e2.last_name
1	Georgi	Facello	12	Patricio	Bridgland
2	Bezalel	Simmel	17	Cristinel	Bouloucos
2	Bezalel	Simmel	18	Kazuhide	Peha
2	Bezalel	Simmel	23	Bojan	Zallocco
2	Bezalel	Simmel	26	Yongqiao	Berztiss
3	Parto	Bamford	18	Kazuhide	Peha
3	Parto	Bamford	21	Ramzi	Erde
3	Parto	Bamford	24	Sumant	Peac
3	Parto	Bamford	25	Shailendra	Mishra

在以上的查询中，使用了WHERE e1.emp_no != e2.emp_no，表示查询一个员工编号不等于另一个员工编号的结果。使用WHERE e1.emp_no = e2.emp_no，表示查询拥有相同上司人数大于等于两个人的结果。最后的查询结果为拥有相同上司（下属）的员工信息集合。