SQL总结（七）查询实战

给定一个场景，学生选课系统为例，大家很熟悉。

主要关系：

学生（学号、姓名、年龄、性别）

教师（教师ID，教师姓名）

课程（课程ID,课程名称,任教教师ID）

成绩（学生ID,课程ID，成绩）

创建关系表：

--学生:Student(SID,SName,SAge,SSex)--学生表（学号、姓名、年龄、性别）--性别,0表示男,1表示女----IF EXISTS(SELECT OBJECT_ID('Student')) /*此处永远为true，原因是OBJECT_ID返回具体ID，或者NULL*/--使用下列语句，如果没有，什么都不返回，也就不存在IF EXISTS(SELECT id FROM sysobjects WHERE name='Student')    DROP Table StudentCreate table Student(    SID nvarchar(20) PRimary key not null,    SName nvarchar(20),    SAge int,    SSex bit)--教师：Teacher(TID,TName)--教师表（教师ID，教师姓名）IF EXISTS(SELECT id FROM sysobjects WHERE name='Teacher') Drop table Teacher GOCreate table Teacher(    TID nvarchar(20) primary key not null,    TName nvarchar(20) not null,    )--课程：Course(CID,CName,TID)--课程表（课程ID,课程名称,任教教师ID）IF EXISTS(SELECT id FROM sysobjects WHERE name='Course') BEGIN    DROP Table CourseENDCreate table Course(    CID  nvarchar(20)  primary key not null,    CName nvarchar(50) not null,    TID nvarchar(20))IF EXISTS(SELECT id FROM sysobjects WHERE name='SC') DROP TABLE SC--成绩：SC(SID,CID,Score)--成绩表（学生ID,课程ID，成绩）Create table SC(    SID nvarchar(20) not null,    CID nvarchar(20) not null,    Score int)alter table SC add constraint PK_SC primary key(SID,CID)

这里仅仅是个例子，针对不同的题目，可以预置适当的数据进行检测。

/*预置数据*/DELETE FROM StudentINSERT INTO Student(SID,SName,SAge,SSex) VALUES('S001','Tom','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S002','Lucy','21','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S003','Jim','18','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S004','Brush','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S005','Kim','22','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S006','Fka','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S007','Cidy','17','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S008','YouNi','19','0')GODELETE FROM TeacherINSERT INTO Teacher(TID,TName) VALUES('T001','张三')INSERT INTO Teacher(TID,TName) VALUES('T002','李四')INSERT INTO Teacher(TID,TName) VALUES('T003','王五')GODELETE FROM CourseINSERT INTO Course(CID,CName,TID) VALUES('C01','英语','T001')INSERT INTO Course(CID,CName,TID) VALUES('C02','体育','T002')INSERT INTO Course(CID,CName,TID) VALUES('C03','数学','T003')GODELETE FROM SCINSERT INTO SC(SID,CID,Score) VALUES('S001','C01','78')INSERT INTO SC(SID,CID,Score) VALUES('S001','C02','60')INSERT INTO SC(SID,CID,Score) VALUES('S001','C03','97')INSERT INTO SC(SID,CID,Score) VALUES('S002','C01','56')INSERT INTO SC(SID,CID,Score) VALUES('S003','C01','55')INSERT INTO SC(SID,CID,Score) VALUES('S004','C01','55')GO

以下题目，希望是一种练习题，是对具体SQL查询方法的具体应用。对于一些复杂查询，也进行分步求解，希望不只是明白了一道题的解法，培养一种解题思路。

以后遇到类似的问题就能轻易破解。

答案默认隐藏，意在希望读者在思考之后，再看参考答案。

当然参考答案也不一定完全正确，或许还有更优解，如果你发现了，请提出。

--1) 最笨的方法--分别得到C01成绩单和C02课程的成绩单,然后再得到C01课程比C02课程高的学生学号SELECT SID,Score FROM SC WHERE CID='C01' SELECT SID,Score FROM SC WHERE CID='C02' SELECT A.SID FROM (SELECT SID,Score FROM SC WHERE CID='C01') AS AINNER JOIN(SELECT SID,Score FROM SC WHERE CID='C02') AS BON A.SID = B.SID WHERE A.Score>B.Score

SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING AVG(Score)>60 

--1)通过Group查询总成绩和选课数,然后再联表查询SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SIDSELECT Student.SID,SName,CourseCount,SumScore FROM Student LEFT JOIN (SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SID) AS B ON Student.SID = B.SID--2）联表查询后再GROUP BySELECT Student.SID,Student.Sname,COUNT(SC.CID),SUM(Score)FROM Student LEFT OUTER JOIN SC ON Student.SID=SC.SIDGROUP BY Student.SID,Sname

SELECT COUNT(DISTINCT(TID)) FROM Teacher WHERE TName LIKE '李%'

--1)查询没有学过“张三”老师课的同学的学号,然后再查询得到学生姓名SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='张三'SELECT SID,Sname FROM Student WHERE SID NOT IN (SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='张三')--2）先查询张三老师的所有课程,然后查询选择了张三老师课程的学生ID，最后查询未选其课程的学生信息SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='张三'SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='张三') AS TeacherCID ON SC.CID = TeacherCID.CIDSELECT SID,SName FROM Student WHERE SID NOT IN(SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='张三') AS TeacherCID ON SC.CID = TeacherCID.CID)--3)查询多表,获取张三老师的课程SELECT Student.SID,Student.SName FROM Student WHERE SID NOT IN (SELECT DISTINCT(SC.SID) FROM SC,Course,Teacher WHERE  SC.CID=Course.CID and Teacher.TID=Course.TID and Teacher.Tname='张三')

--1)查询有课程不及格的学生IDSELECT DISTINCT(SID) FROM SC WHERE Score<60SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING  COUNT(SID)>2  AND SID IN (SELECT DISTINCT(SID) FROM SC WHERE Score<60)--2)查询有两门以上不及格的学号SELECT SID FROM SC WHERE Score<60 GROUP B