1.簡介
自連接查詢是內(nèi)連接中的一種特殊查詢姚淆。
它是指相互連接的表在物理上為同一個表掂僵，但邏輯上分為兩個表。
2.準(zhǔn)備
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);
s27_1.png
3.案例
查找和a1同一個gid的數(shù)據(jù)
SELECT * FROM student s1
JOIN student s2
ON s2.`name`='a1' AND s1.gid = s2.gid;
s27_2.png
去掉不要的列
SELECT s1.id,s1.name FROM student s1
JOIN student s2
ON s2.`name`='a1' AND s1.gid = s2.gid
s27_3.png
