以选课为例的复杂sql查询，以后备用

 

 --1查询“大学物理”课程比“历史”课程成绩高的所有学生的学号；	 
	 --（把课程A作为一个表，课程2作为一个表，然后A表B表一同学，分数A>B）
	 select A.S# from 
	 (select  s#,score FROM SC WHERE C#='dxwl') A ,(select  s#,score FROM SC WHERE C#='ls') B
	 where A.Score>B.Score and A.S#=B.S# 
	 
	 --2查询平均成绩大于60分的同学的学号和平均成绩；
	 select S# ,AVG(Score) from SC group by S# having Avg(Score)>41
	 
	 --3查询所有同学的学号、姓名、选课数、总成绩；
	 select S#,sname,COUNT(C#) as CourseCount,SUM(Score) SumScore from  V_StudentCourseScore group by S#,sname
	
	--9、查询所有课程成绩小于分的同学的学号、姓名；
	select S#,Sname from Student where S# not in(select distinct(S#) from SC where Score>=60)
	 
	 --查询没有学全所有课的同学的学号、姓名； 
	 --（如果查询两个表， 先用where关联后相当于一个表）
	 select Student.S#,Sname 
	 from Student,SC
	 where Student.S#=SC.S#
	 group by  Student.S#,Sname 
	 having COUNT(C#) < (select COUNT(1) from Course)
	 
	 --查询至少有一门课与学号为“lyp”的同学所学相同的 同学的学号和姓名；
	  select Student.S#,Sname 
	 from Student,SC
	 where Student.S#=SC.S#
	 and C# in (select C# from SC where S#='lyp')
	 
	 --查询和“lyp”号的同学学习的课程完全相同的其他同学学号和姓名；
	 --(首先只要把和lyp所学课程内的所有同学查出，然后按人统计，课程门数相同)
	 select S# from SC
	 where C# in(select C# from SC where S#='lyp')
	 group by S#
	 having COUNT(C#)=(select COUNT(C#) from SC where S#='wqd')

 --转置查询
  --按平均成绩从高到低显示所有学生的“大学物理”、“历史”、“人工智能”三门的课程成绩，
  --按如下形式显示：学生ID,,大学物理,历史,人工智能,有效课程数,有效平均分
   SELECT S# as 学生ID  
       ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='dxwl') AS 大学物理 
       ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='ls') AS 历史 
       ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#='rgzn') AS 人工智能 
       ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 
   FROM SC AS t  
   GROUP BY S#  
   ORDER BY avg(t.score) 
   
   
    --查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
    select m.C# [课程编号], m.Cname [课程名称],  
  (select max(score) from SC where C# = m.C#) [最高分], 
  (select min(score) from SC where C# = m.C#) [最低分], 
  (select cast(avg(score) as decimal(18,2)) from SC where C# = m.C#) [平均分] 
  from Course m  
  order by m.C#
 
 --查询各科成绩前三名的记录:(不考虑成绩并列情况) 
 SELECT      S# AS 学生ID, C# AS 课程ID, Score AS 分数
 FROM       SC AS t1
 WHERE     (Score IN
         (SELECT     TOP (3) Score
        FROM          dbo.SC
        WHERE      (t1.C# = C#)
        ORDER BY Score DESC))
 ORDER BY 课程ID