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:查询“语文”和“数学”都及格的学生的编号和姓名
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