Mysql自连接的一些用法

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

我们举例说明，下面是商品采购表，我们需要找到采购价格比惠惠高的所有信息。

一般情况我们看到这张表我们第一时间用语句进行操作：

 1 SELECT * FROM shoping WHERE price>27 

可想而知，这是有多么简单，假设你并不知道数据库表详细数据或者数据量相当庞大呢？作为一个数据库管理员，我们就要用别的方式迅速找出所需要的数据。

分步查询

1 2	SELECT price FROM shopping WHERE name='惠惠' //得出price查询结果为27 SELECT * FROM shopping WHERE price>27

我们可以获取到如下表信息：

与采用自连接的方式相比，这种方法需要对中间结果进行人工干预，显然不利于程序中的自动处理操作。

自连接方式：

1 2 3 4 5

SELECT b.* from shopping as a,shopping as b where a.name='惠惠' and a.price<b.price order by b.id2018-03-06

获取到如下表信息：　　

注意点：

别名 a,b虽然名称不同，但是同一个表，定义别名的目的是更方便在自身进行删选。

执行select通过（中间表）所得到的b.*，，就是最终结果。

再举个实例：

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE dept( -- 部门表 deptno INT PRIMARY KEY,-- 部门编号 dname VARCHAR(14),-- 部门名称 loc VARCHAR(13)-- 地点 ); ENGINE=INNODB DEFAULT CHARSET=utf8;   INSERT INTO dept VALUES(10, 'Accounting', 'New York') ; INSERT INTO dept VALUES(20, 'Research', 'Dallas') ; INSERT INTO dept VALUES(30, 'Sales', 'Chicago') ; INSERT INTO dept VALUES(40, 'Operations', 'Boston') ; INSERT INTO dept VALUES(50, 'Admin', 'Washing') ;<br><br>

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE emp( -- 员工表 empno INT NOT NULL PRIMARY KEY,-- empno员工号 ename VARCHAR(10), -- ename员工姓名 job VARCHAR(10),-- job工作 mgr INT,-- mgr上级人员编号 hiredate DATETIME,-- 受雇日期 sal DOUBLE,-- 薪金 comm DOUBLE,-- 佣金 deptno INT,-- 部门编号 FOREIGN KEY(deptno) REFERENCES dept(deptno) ); ENGINE=INNODB DEFAULT CHARSET=utf8;<br><br>

1 2 3 4 5 6 7 8

INSERT INTO emp VALUES(7369, 'Smith', 'Clerk',7902, '1980-12-17',800,0,20) ; INSERT INTO emp VALUES(7499, 'Allen', 'Salesman',7698,'1981-2-20',1600,300,30) ; INSERT INTO emp VALUES(7844, 'Turner', 'Salesman',7499, '1981-9-8',1500,0,30) ; INSERT INTO emp VALUES(7698, 'Tom', 'Manager',0, '1981-9-8',6100,600,40) ; INSERT INTO emp VALUES(7876, 'Adams', 'Clerk',7900, '1987-5-23',1100,0,20) ; INSERT INTO emp VALUES(7900, 'James', 'Clerk',7698, '1981-12-3',2400,0,30) ; INSERT INTO emp VALUES(7902, 'Ford', 'Analyst',7698, '1981-12-3',3000,NULL,20) ; INSERT INTO emp VALUES(7901, 'Kik', 'Clerk',7900, '1981-12-3',1900,0,30) ;

　　

　　

打开创建好的表：

如果说列出所有员工的姓名及其直接上级的姓名，我们可以通过自连接进行这样的操作：

1	SELECT e.ename,(SELECT ename FROM emp d WHERE d.empno=e.mgr) AS boss FROM emp e;

查询的结果：

上面的是一张职员信息表,如果我要查询这张表中的每个职员的上司,那么必须使用自连接来查询.所以为了能实现这个查询,需要给这张表取两个别名,而且查询中所用的所有数据都需要加上表的别名前缀,因为两个表的数据列完全一样.

转自：https://www.cnblogs.com/weichao1996/articles/8519973.html