mysql自连接

自连接

1、什么是自连接？
“自连接”连接的是同一张表，即自己与自己连接；

2、自连接什么时候用？

看下面的例题
例1：查询同时选修了c01和c04的学生的学号(sc)
例2：查询选修了课程c01或c04的学生的学号
错误的写法：

1、SELECT sno FROM sc WHERE cno="c01" AND cno="c04";
2、SELECT sno FROM sc WHERE cno="c01" OR cno="c04";

使用自连接的正确的写法：

SELECT a.sno FROM sc a,sc b WHERE a.sno=b.sno AND a.cno="c01" AND b.cno="c04";

(子查询也适用，其余不适用)

例3：查询c01的课程比c04课程高的学生的学号以及c01和c04对应的课程成绩；

SELECT a.sno,a.cno,a.degree,b.cno,b.degree FROM sc a,sc b 
WHERE a.sno=b.sno AND a.cno="c01" AND b.cno="c04" AND a.degree>b.degree;

什么时候使用自连接：
(1)条件发生在同一列（同一个字段）；
回顾刚刚的题目：发现要求的两个条件，课程为c01和c04发生在同一列
自连接的思路相似于内连接：
(1)找到涉及的表
(2)找到具体相同意义的字段，作为关系字段
(3)加上筛选条件：连接之后的多张表看作一张新表加上筛选条件
注：自连接时需要把一张表看作两张表；并且自连接一定要给表起别名；

题型巩固

1、查询同时选修了a01和a02两门课程的学生的学号；
2、查询a01课程成绩比a02课程成绩高的学生的学号以及a01，a02对应的成绩；
3、查询选修同时选修了a01和a02以及c01的学生的学号；
4、查询选修同时选修了a01和a02以及c01的学生，并且a01成绩高于a02,a02成绩高于co1;

1、SELECT a.sno FROM sc a,sc b 
WHERE a.sno=b.sno AND a.cno='a01' AND b.`cno`='a02';

2、SELECT a.sno,a.degree,b.sno,b.degree FROM sc a,sc b 
WHERE a.cno=b.cno AND a.cno='a01' OR a.cno='a02' AND a.degree>b.degree;

3、SELECT a.sno FROM sc a,sc b,sc c 
WHERE a.sno=b.sno AND b.sno=c.sno AND a.cno='a01' AND b.cno='a02' OR c.cno='c01';

4、SELECT a.sno FROM sc a,sc b,sc c 
WHERE a.sno=b.sno AND b.sno=c.sno AND a.cno='a01' AND b.cno='a02' 
AND c.cno='c01' AND a.degree>b.degree AND b.degree>c.degree;

占列与非占列排序（难）

若所写的内容有误或者有更好的写法，欢迎留言

（1）用一条sql语句给sc表的每门课程成绩进行排名（考虑成绩并列占位这种情况）；
并列占位的意思：比如名次是：1，2，2，4…因为有两个第二名，所以就占了第三名的位置（实际当中的排名）
另一种就是并列了不占位置，名次就是：1，2，2，3…

select a.sno,a.cno,a.degree,(select count(distinct degree)+1 from sc 
where cno=a.cno and degree>a.degree) as rank from sc a 
order by a.cno,a.degree desc;

（2）一条sql语句给sc表的每门课程成绩进行排名（考虑成绩并列不占位这种情况）；
涉及到改一个字段

select a.sno,a.cno,a.degree,(select count(degree)+1 from sc 
where cno=a.cno and degree>a.degree) as rank from sc a 
order by a.cno,a.degree desc;

（3）查询每科前三名的学生的学号，选修的课程以及对应的成绩（考虑成绩并列占位这种情况）；

select a.sno,(select count(degree)+1 from sc 
where cno=a.cno and degree>a.degree) as rank from sc a 
order by a.cno,a.degree limit 0,3;

（4）查询sc表里各科成绩第二高的学生的学号，选修的课程号以及对应的成绩；

select sno,cno,degree (select count(degree)+1 from sc 
where cno=a.cno and degree>a.degree) as rank from sc a 
order by a.cno,a.degree limit 1,1;