27.MySQL中的自连接

1.简介

自连接查询是内连接中的一种特殊查询。

它是指相互连接的表在物理上为同一个表，但逻辑上分为两个表。

2.准备

 1 CREATE DATABASE mahaiwuji;
 2 USE mahaiwuji;
 3 
 4 CREATE TABLE student(
 5     id INT(4) PRIMARY KEY,
 6     name VARCHAR(36),
 7     gid INT(4)
 8 ) ENGINE = INNODB DEFAULT CHARSET = utf8;
 9 
10 INSERT INTO student VALUES (1,'a1',1);
11 INSERT INTO student VALUES (2,'a2',1);
12 INSERT INTO student VALUES (3,'a3',1);
13 INSERT INTO student VALUES (4,'a4',1);
14 INSERT INTO student VALUES (5,'a5',2);
15 INSERT INTO student VALUES (6,'a6',2);
16 INSERT INTO student VALUES (7,'a7',2);
17 INSERT INTO student VALUES (8,'a8',2);

3.案例

查找和a1同一个gid的数据

1 SELECT * FROM student s1
2 JOIN student s2
3 ON s2.`name`='a1' AND s1.gid = s2.gid;

去掉不要的列

1 SELECT s1.id,s1.name FROM student s1
2 JOIN student s2
3 ON s2.`name`='a1' AND s1.gid = s2.gid

posted @ 2020-04-18 14:48  码海无际  阅读(400)  评论(0)    收藏  举报