有如下几个表：学生基础信息表（学号，姓名等），学生综合信息表（学号，姓名，惩罚分值，奖励分值），学生成绩表（学号，课程号，课程成绩）、学生品行成绩表（学号，辅导员，班主任、班级评议）、学生奖学金表（学号，姓名，综测成绩，奖学金等级）
请问该如何完成以下判断：
第一步：先用学生综合信息表里的学号来对照学生基础信息表查询是否存在该学生，若存在则进入下一步；
第二步：判断学生是否有不及格科目（这里可以通过判断学生成绩表里的每个学生的课程成绩是否有小于60的（另外成绩表里有一门课程号是体育的课程，这项的成绩不能小于80））和判断每个学生在综合信息表里的惩罚分值不能大于零来过滤一部分学生，然后进入下一步；
第三步：将满足上两步条件的学生进行综测成绩的求取：通过学生成绩表得出每个学生的课程成绩总和并取平均数，通过学生品行成绩表将（辅导员、班主任、班级评议）三项相加并向上取整取平均值【我这里用的是cast(round((辅导员+班主任+班级评议)/3.0,0)as int) as 品行成绩】得品行成绩，把每个学生对应的“课程成绩平均值”和“品行成绩”和“奖励分值”相加即得每个学生对应的“综测成绩”；
最后一步：根据每个学生的综测成绩排名并赋予相应的“奖学金等级”：【例如第一名“校长奖学金”，第二名“特等奖学金”，第三名“一等奖学金”，第四、第五名“二等奖学金”，第六、七、八名“三等奖学金”，第九、十名“优秀学生”】，最后将每个学生对应的学号、姓名、综测成绩、奖学金等级录入到学生奖学金表中，至此结束。
我感觉这个有点复杂，尤其是相加和排名那里，所以请大家指点一下这个该怎么写呢
大概这样吧~
--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
avg 取平均
1、不考虑 综测成绩 相同的情况，如果最后需要的是insert语句需要自己转换一下
WITH t1 AS (
SELECT a.学号, a.姓名, b.惩罚分值, b.奖励分值 FROM 学生基础信息表 a JOIN 学生综合信息表 b ON a.学号 = b.学号 )
, t2 AS (
SELECT * FROM t1 a WHERE 惩罚分值 <= 0 AND NOT EXISTS( SELECT 1 FROM 学生成绩表 b WHERE a.学号 = b.学号 AND b.课程成绩 < ( CASE WHEN b.课程号 = '体育' THEN 80 ELSE 60 END ) )
, t3 AS (
SELECT a.学号, a.姓名, a.奖励分值
+ CAST( ( c.辅导员 + c.班主任 + c.班级评议 ) / 3.0 AS int ) /* 品行成绩 */
+ avg( 课程成绩 ) /* 课程成绩平均值 */ AS 综测成绩
FROM t2 a JOIN 学生成绩表 b ON a.学号 = b.学号
JOIN 学生品行成绩表 c ON a.学号 = c.学号
GROUP BY a.学号, a.姓名, a.奖励分值, c.辅导员, c.班主任, c.班级评议 )
, t4_0 AS (
SELECT 1 rn, '校长奖学金' nam UNION ALL SELECT 2, '特等奖学金' UNION ALL SELECT 3, '一等奖学金' UNION ALL
SELECT 4, '二等奖学金' UNION ALL SELECT 5, '二等奖学金' UNION ALL SELECT 6, '三等奖学金' UNION ALL
SELECT 7, '三等奖学金' UNION ALL SELECT 8, '三等奖学金' UNION ALL SELECT 9, '优秀学生' UNION ALL SELECT 10, '优秀学生' )
, t4_1 AS (
SELECT *, ROW_NUMBER( ) OVER( ORDER BY 综测成绩 DESC ) rn FROM t3 )
SELECT b.学号, b.姓名, b.综测成绩, a.nam 奖学金等级 FROM t4_0 a JOIN t4_1 b ON a.rn = t4_1.rn
2、实际上 综测成绩 有可能存在相同的情况，这就需要看需求如何处理，是只考虑10个人拿奖学金，还是考虑前10的所有人拿奖学金，如果是前者，需要增加其他条件进行进一步排序筛选；如果是后者，需要将其中 ROW_NUMBER( ) 函数修改为：DENSE_RANK( )即可
您好，我是有问必答小助手，您的问题已经有小伙伴帮您解答，感谢您对有问必答的支持与关注！