1、自连接可以把同一表中属于一列的数据，平移到一行中，然后进行比较或其他操作。
实例：有两张表学生信息表和分数表；
结构和数据如下：
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for zhongxin_student -- ---------------------------- DROP TABLE IF EXISTS `zhongxin_student`; CREATE TABLE `zhongxin_student` ( `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘姓名‘, `age` int(11) NULL DEFAULT NULL COMMENT ‘年龄‘, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of zhongxin_student -- ---------------------------- INSERT INTO `zhongxin_student` VALUES (‘1‘, ‘张三‘, 11); INSERT INTO `zhongxin_student` VALUES (‘2‘, ‘李四‘, 22); SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for zhongxin_score -- ---------------------------- DROP TABLE IF EXISTS `zhongxin_score`; CREATE TABLE `zhongxin_score` ( `id` int(11) NOT NULL, `sid` int(11) NULL DEFAULT NULL COMMENT ‘学生id‘, `course_id` int(11) NULL DEFAULT NULL COMMENT ‘课程id‘, `score` int(11) NULL DEFAULT NULL COMMENT ‘分数‘, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of zhongxin_score -- ---------------------------- INSERT INTO `zhongxin_score` VALUES (1, 1, 1, 70); INSERT INTO `zhongxin_score` VALUES (2, 1, 2, 80); INSERT INTO `zhongxin_score` VALUES (3, 2, 1, 80); INSERT INTO `zhongxin_score` VALUES (4, 2, 2, 70); SET FOREIGN_KEY_CHECKS = 1;
数据概览：
student：
score：
要求：查询课程编号1比课程编号2分数高的学生姓名
sql语句：
//查询‘1’科目成绩比‘2’科目成绩高的学生姓名 select zs.name from zhongxin_student zs join (select sid,score from zhongxin_score where course_id=1)as a on a.sid=zs.id join (select sid,score from zhongxin_score where course_id=2)as b on b.sid=zs.id where a.score>b.score
结果：