SQL Server几个小逻辑查询

数据库三个表

学生表Student(SNo, SName, SAvg)

分数表Grade(CNo, SNo, CScore)

课程表Course(CNo, CName)

1: 查询学生的平均分，并且更新到Student的SAvg中

   1:  update Student set SAvg=us.avg

   2:   

   3:  from 

   4:   

   5:  (

   6:   

   7:  select s.SNo,AVG(g.CScore) as avg from 

   8:   

   9:  Student s left join Grade g

  10:   

  11:  on s.SNo=g.SNo

  12:   

  13:  group by s.SNo,s.SName

  14:   

  15:  ) us

  16:   

  17:  where Student.SNo=us.SNo

执行结果：

 

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

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

   2:   

   3:  (

   4:   

   5:  select a.SNo from

   6:   

   7:  (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='语文') a,

   8:   

   9:  (select SNo,CScore from Grade g left join Course c on g.CNo=c.CNo where CName='数学') b

  10:   

  11:  where a.CScore>b.CScore and a.SNo=b.SNo

  12:   

  13:  group by a.SNo

  14:   

  15:  )

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

   1:  select t.SNo as 学生编号,s.SName as 学生姓名,s.SAvg as 平均分,t.及格门数,t.总门数,t.及格门数/(t.总门数+0.0) as 及格率 from

   2:   

   3:  (

   4:   

   5:  select SNo,COUNT(CNo) as 总门数,

   6:   

   7:  sum(

   8:   

   9:  case

  10:   

  11:  when CScore>=60 then 1

  12:   

  13:  when CScore<60 then 0

  14:   

  15:  end

  16:   

  17:  ) as 及格门数

  18:   

  19:  from Grade group by SNo

  20:   

  21:  ) t left join Student s on t.SNo=s.SNo

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

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

   2:   

   3:  (

   4:   

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

   6:   

   7:  and exists

   8:   

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

  10:   

  11:  )

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

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

   2:   

   3:  (

   4:   

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

   6:   

   7:  from Grade where SNo in

   8:   

   9:  (

  10:   

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

  12:   

  13:  ) group by SNo

  14:   

  15:  ) tb

  16:   

  17:  union

  18:   

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

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

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

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

   2:   

   3:  where CScore in

   4:   

   5:  (

   6:   

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

   8:   

   9:  )

  10:   

  11:  order by g1.CNo

转载于:https://www.cnblogs.com/lovexiao/archive/2013/04/17/3026819.html