关键词

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,表示查询拥有相同上司人数大于等于两个人的结果。最后的查询结果为拥有相同上司(下属)的员工信息集合。

本文链接:http://task.lmcjl.com/news/13969.html

展开阅读全文