1.简介
自连接查询是内连接中的一种特殊查询。
它是指相互连接的表在物理上为同一个表,但逻辑上分为两个表。
2.准备
CREATE DATABASE mahaiwuji;USE mahaiwuji;CREATE TABLE student( id INT(4) PRIMARY KEY, name VARCHAR(36), gid INT(4)) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO student VALUES (1,'a1',1);INSERT INTO student VALUES (2,'a2',1);INSERT INTO student VALUES (3,'a3',1);INSERT INTO student VALUES (4,'a4',1);INSERT INTO student VALUES (5,'a5',2);INSERT INTO student VALUES (6,'a6',2);INSERT INTO student VALUES (7,'a7',2);INSERT INTO student VALUES (8,'a8',2);
3.案例
查找和a1同一个gid的数据
SELECT * FROM student s1JOIN student s2ON s2.`name`='a1' AND s1.gid = s2.gid;
去掉不要的列
SELECT s1.id,s1.name FROM student s1JOIN student s2ON s2.`name`='a1' AND s1.gid = s2.gid