根据不同情况给学生评分

Question

嗨，所有人，

我在不同的科目上有学生的分数。

表 #Maths 包含名为 a，b 和 c 的学生的数学分数。#Science和#English类似。

现在我想要的输出是，如果学生在两个科目上超过75分，他将被评为“优秀”。如果他的一门课超过75分，另一门课超过60分，那么他的成绩就是“及格”，如果没有一门课超过75分，而有一门课不到50分，那么他的成绩就是“不及格”。

请为结果提供解决方案？

谢啦

Answer 1

您可以尝试以下查询：

• 此查询肯定比Joins更快，因为它减少了case条件的操作集
• 业务逻辑被巧妙地封装为权重和顶级查询条件的一部分
• 无连接，因此速度更快。当你开始增加学生记录和科目表时，速度将变得明显

查询：

select 
  id, 
  name, 
  case 
       when sum(weightRank) >=32 then "Merit"
       when sum(weightRank) >=20 then "Pass"
       when sum(weightRank) <16 and  count(ALL weightRank) < count(weightRank)    
       then "Fail"
       else "N/A"
  end as grade
from
 (
    select id,name,'m' as subject,marks from #maths
        union all 
    select id,name,'m' as subject,marks from #science
        union all
    select id,name,'m' as subject,marks from #english 
) allmarks

-- allmarks get all records together, and is faster than joining all tables
-- this is also extensible as subjects and students may increase
-- and not all subjects may have marks for all students, so we will not lose data as in case of joins

    join 
        (values (NULL,0, 50),(1,51, 60),(4,61, 75), (16,76,100) 
        as I(weightRank,lowNumber, highNumber)

-- here we create a temp dynamic table to weight the marks 

        on 
            allmarks.marks between I.lowNumber AND I.HighNumber

group by id,name

Answer 2

SELECT m.id, m.name, m.marks, s.marks, e.marks,
CASE WHEN (m.marks > 75 and s.marks > 75) OR (s.marks > 75 and e.marks > 75) OR (m.marks > 75 and e.marks > 75) THEN 'Pass'
     WHEN (m.marks > 75 and (s.marks > 60 OR e.marks > 60)) OR
          (s.marks > 75 and (m.marks > 60 OR e.marks > 60)) OR
          (e.marks > 75 and (s.marks > 60 OR m.marks > 60)) THEN 'Pass'
     WHEN (m.marks < 75 and s.marks < 75 and e.marks < 75 and (m.marks < 50 or e.marks < 50 or s.marks < 50)) THEN 'Fail'
     ELSE NULL END as Merit
FROM #maths m
inner join #science s
on m.ID = s.ID
inner join #English e
on m.ID = e.ID

不太经典，但因为只有几列需要比较，我会这样做