SQL-基于派生表的查询

　　子查询不仅可以出现在FROM子句中WHERE子句中，还可以出现在FROM子句中，这是子查询生成的临时派生表成为主查询的查询对象。如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。

　　通过FROM子句生成派生表时，AS关键字可以省略，但必须为一个派生关系指定一个别名。

例 1：求出每个学生超过他自己选修课程平均成绩的课程号

方法一

Select Sno，Cno

From SC X

Where Grade>（Select AVG(Grade)

               From SC Y

               Where Y.Sno=X.Sno);　

方法二

SELECT Sno,Cno

    FROM SC,(SELECT Sno,Avg(Grade) FROM SC GROUP BY Sno)

        AS Avg_sc(avg_sno,avg_grade)

WHERE SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade;

这里FROM子句中的子查询将生成一个派生表Avg_sc。该表有avg_sno和avg_grade两个属性组成，记录了每个学生的学号及平均成绩。主查询将SC表与AVG_sc按学号相等进行连接，选出选修成绩大于其平均成绩的课程号。

 

例 2：查询成绩在90分以上的女生的姓名

    select Sname

    from Students S, (select Sid from SC where grade>90) T

    where Ssex='F' and S.sid=T.sid;

 

思考题： 求选修课程记录中，男生里成绩最好和最差的学生的姓名，性别和成绩，以及女生里成绩最好和最差的学生的姓名，性别和成绩

select S.sname,S.ssex,SC.grade 
    
    from student S,SC,(Select student.ssex, max(grade) 

        From student, SC Where student.sno=SC.sno Group by student.ssex)

            as T(ssex,grade)

                where S.sno=SC.sno and S.ssex=T.ssex and SC.grade=T.grade

Union

select S.sname,S.ssex,SC.grade
    
    from student S,SC,(Select student.ssex, min(grade) 

        From student, SC Where student.sno=SC.sno Group by student.ssex)

        as T(ssex,grade)

            where S.sno=SC.sno and S.ssex=T.ssex and SC.grade=T.grade；