表的自身连接原理详解

以问题驱动的方式来讲解

create table stu
(
  sno char(10) not null,
  sname char(20) not null,
  cname char(20) not null,
  score decimal(3,1) not null
);
insert into stu values('001','王军','计网',70.0);
insert into stu values('001','王军','算法',70.0);
insert into stu values('002','李伟','计网',80.9);
insert into stu values('002','李伟','算法',59.0);
insert into stu values('003','刘辉','计网',56.0);
insert into stu values('003','刘辉','算法',56.0);

查询成绩表中存在不及格课程的学生姓名，所有课程名和成绩信息

刚开始上来就敲了这个

select sname,cname,score
from stu
where score<60;

只显示成绩小于60的课程，不是所有课程，这时就用到表的自身连接了

select *
from stu as s1,stu as s2
where s1.sno=s2.sno;

表的自身连接就是产生2个表的笛卡尔积

select s1.sname,s1.cname,s1.score
from stu as s1,stu as s2
where s1.sno=s2.sno
and s2.score<60
order by s1.sname;

就是上图最后6行的数据

有重复的语句所以要去重

select distinct s1.sname,s1.cname,s1.score
from stu as s1,stu as s2
where s1.sno=s2.sno
and s2.score<60
order by s1.sname;

完成

再来个例子，具体的原理就不讲了

查询每一门课的先修课的先修课

course表有2列con（课程号）,cpno（先修课程号）

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno