SQL Server几个小逻辑查询

数据库三个表

学生表Student(SNo, SName, SAvg)

分数表Grade(CNo, SNo, CScore)

课程表Course(CNo, CName)

1: 查询学生的平均分，并且更新到Student的SAvg中执行结果：

update Student set SAvg=us.avg
 from 
 (
 select s.SNo,AVG(g.CScore) as avg from 
 Student s left join Grade g
 on s.SNo=g.
 ) us
 where Student.SNo=us.SNo

　　

 

2：查询'语文'成绩比'数学'成绩高的所有学生的学生编号，学生姓名

 

 

	 select SNo as 学生编号,SName as 学生姓名 from Student where SNo in 
 	( 
 	select a.SNo from 
	 (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='语文') a, 
	 (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='数学') b 
 	 where a.CScore>b.CScore and a.SNo=b.SNo 
 	 group by a.SNo 
 	 )

 

　　

3：查询每个学生的选课数，及格率(及格的课程数\选课数)

 

		 select t.SNo as 学生编号,s.SName as 学生姓名,s.SAvg as 平均分,t.及格门数,t.总门数,t.及格门数/(t.总门数+0.0) as 及格率 from 
       ( 
       select SNo,COUNT(CNo) as 总门数, 
       sum( 
       case 
        when CScore>=60 then 1 
        when CScore<60 then 0 
        end 
        ) as 及格门数 
        from Grade group by SNo 
        ) t left join Student s on t.SNo=s.SNo

 

　　

4:查询“语文”和“数学”都及格的学生的编号和姓名

select SNo as 学生编号,SName as 学生姓名 from Student where SNo in

(

select SNo from Grade g left join Course c on g.CNo=c.CNo where CName='语文' and CScore>60

and exists

(select SNo from Grade g left join Course c on g.CNo=c.CNo where CName='数学' and CScore>60)

)

5：去掉最高分和最低分算平均成绩，如果少于三门直接算平均数

select SNo as 学生编号, (tb.sm-tb.mx-tb.mn)/(tb.ct-2) as 平均分 from

(

select SNo,COUNT(CScore) as ct, SUM(CScore) as sm,MAX(CScore) as mx,MIN(CScore) as mn

from Grade where SNo in

(

select SNo from Grade group by SNo having Count(CNo)>=3

) group by SNo

) tb

union

select SNo, AVG(CScore) from Grade group by SNo having count(CScore)<3

根据需要我又添加了一条数据王五，只选修语文和数学

5：查询每门功课成绩最好的前两名

select SNo as 学生编号,CNo as 课程编号,CScore as 分数 from Grade g1

where CScore in

(

select top 2 CScore FROM Grade WHERE g1.CNo=CNo ORDER BY CScore DESC

)

order by g1.CNo