对学生综合成绩处理并得出相应等级

Question

  有如下几个表：学生基础信息表（学号，姓名等），学生综合信息表（学号，姓名，惩罚分值，奖励分值），学生成绩表（学号，课程号，课程成绩）、学生品行成绩表（学号，辅导员，班主任、班级评议）、学生奖学金表（学号，姓名，综测成绩，奖学金等级）
  请问该如何完成以下判断：
  第一步：先用学生综合信息表里的学号来对照学生基础信息表查询是否存在该学生，若存在则进入下一步；
  第二步：判断学生是否有不及格科目（这里可以通过判断学生成绩表里的每个学生的课程成绩是否有小于60的（另外成绩表里有一门课程号是体育的课程，这项的成绩不能小于80））和判断每个学生在综合信息表里的惩罚分值不能大于零来过滤一部分学生，然后进入下一步；
   第三步：将满足上两步条件的学生进行综测成绩的求取：通过学生成绩表得出每个学生的课程成绩总和并取平均数，通过学生品行成绩表将（辅导员、班主任、班级评议）三项相加并向上取整取平均值【我这里用的是cast(round((辅导员+班主任+班级评议)/3.0,0)as int) as 品行成绩】得品行成绩，把每个学生对应的“课程成绩平均值”和“品行成绩”和“奖励分值”相加即得每个学生对应的“综测成绩”；
  最后一步：根据每个学生的综测成绩排名并赋予相应的“奖学金等级”：【例如第一名“校长奖学金”，第二名“特等奖学金”，第三名“一等奖学金”，第四、第五名“二等奖学金”，第六、七、八名“三等奖学金”，第九、十名“优秀学生”】，最后将每个学生对应的学号、姓名、综测成绩、奖学金等级录入到学生奖学金表中，至此结束。

我感觉这个有点复杂，尤其是相加和排名那里，所以请大家指点一下这个该怎么写呢

Answer 1

大概这样吧~

--insert into 学生奖学金表--插入到表中取消注释这句即可
select 学号,姓名,综测成绩,
case--根据排名获取奖励名称
when myrank=1 then '校长奖学金' when myrank=2 then '特等奖学金' when myrank=3 then '一等奖学金' when myrank in(4,5)then '二等奖学金' when myrank in(6,7,8)then '三等奖学金' else '优秀学生'
end  奖学金等级 from(
--ROW_NUMBER生成排名
select 学号,姓名,综测成绩,ROW_NUMBER() over(order by 综测成绩 desc)myrank
from(
select base.学号,base.姓名,score.综测成绩
from 学生基础信息表 base,学生综合信息表 prize ,
(
---获取学生综测成绩
select (cast(round((辅导员+班主任+班级评议)/3.0,0) as int)+平均成绩+奖励分值)as 综测成绩,学生品行成绩表.学号 
from 学生品行成绩表 
left join (
select AVG(课程成绩)as 平均成绩,学号 from 学生成绩表 group by 学号)t on t.学号=学生品行成绩表.学号
left join 学生综合信息表 on 学生综合信息表.学号=学生品行成绩表.学号
)score  
where score.学号 =base.学号 and  base.学号=prize.学号 and prize.惩罚分值=0 and base.学号 not in(select 学号 from 学生成绩表 where 课程成绩<60 group by 学号)
)allinfo )allinfowithpricename where myrank<11