学生重复课程的最大Grade_points

Question

我正在做一个学生注册数据库项目。学生注册课程并获得成绩。有时一个学生重复一门课程，获得更好的成绩。我需要只使用最好的成绩来计算grade_point和学分的总和。所以对于每个在不同学期重复课程的学生，我必须确定最高的成绩是多少。emplid代表学生，course_id标识一门课程，学分是学分hr，Grade_point是字母等级的数值，术语代表学期课程...

下面是一个我正在努力完成的例子。

emplid couse_id credit_hr grade_pt term    
  0001    6001       3        4      Fall15 
  0001    6002       3       3.5     Fall15
  0001    6003       3        2      Fall15
  0001    6004       4       2.5     Sp16
  0001    6002       3       3.0     Sp16 
total(requirrd)      13      12

OP评论中的示例代码：

SELECT a.emplid
      ,a.subject
      ,a.CATALOG_NBR
      ,a.strm
      ,a.CRSE_GRADE_OFF
      ,a.R‌​EPEAT_CANDIDATE
      ,a.un‌​t_taken AS cr
      ,a.CRSE_ID
      ,MAX(a.grade_points)
 OVER (PARTITION BY A.emplid ,crse_id) 
 FROM ps_CLASS_TBL_SE_VW a
 WHERE emplid LIKE '06381313011%'

Answer 1

select emplid, sum(credit_hr), sum(grade_pt)
from (
select emplid, course_id, credit_hr, grade_pt, term,
row_number() over(partition by emplid, course_id
order by grade_pt desc) rn
from your_table
)
where rn = 1
group by emplid 

Answer 2

这里有一个方法可以做到这一点。

问题很复杂，因为它似乎是要显示结果集中的所有输入行，但是不同的聚合应该只考虑一些行。

第一部分很简单：子查询将EMPLID和COURSE\u ID的每个组合的行按收到的成绩降序排列，并根据该顺序为它们分配一个行号（在组内）。

外部查询执行聚合。我使用按汇总分组，这允许很大的灵活性。当“汇总”实际上是每一行本身时，我会显示该行的实际学时和分数，但在汇总中我会加上其他内容：即，当行号为1时，在学时（以及学时乘以分数）上，否则为NULL（在计算SUM（）时，将其视为不存在）。

我创造了第二个员工，他在同一门课程中获得了两次相同的分数（所以我可以检查我的解决方案在这种情况下不会给出错误的答案）。我为每个课程id模拟了一个单独的学分表，并且连接需要在结果中得到学分。此外，我不求和绩点，因为这不是绩点计算所做的；相反，我使用正确的计算，其中每个绩点乘以课程学时，然后这些乘积（仅针对每个课程的最高成绩，分别对于每个EMPLID），都加在一起。

with
     grades ( emplid, course_id, grade_pt, term ) as (
       select '0001', '6001', 4  , 'Fall15' from dual union all
       select '0001', '6002', 3.5, 'Fall15' from dual union all
       select '0001', '6003', 2  , 'Fall15' from dual union all
       select '0001', '6004', 2.5, 'Sp16'   from dual union all
       select '0001', '6002', 3  , 'Sp16'   from dual union all
       select '0003', '6002', 3.5, 'Sp16'   from dual union all
       select '0003', '6003', 2.5, 'Fall16' from dual union all
       select '0003', '6003', 2.5, 'Sp15'   from dual
     ),
     credits ( course_id, credit_hr ) as (
       select '6001', 3 from dual union all
       select '6002', 3 from dual union all
       select '6003', 3 from dual union all
       select '6004', 4 from dual
     )
-- End of simulated inputs (for testing purposes only, not part of the solution).
-- SQL query begins BELOW THIS LINE.
select emplid, course_id,
       case when grouping(term) = 0 then credit_hr
            else sum(case when rn = 1 then credit_hr end) end as credit_hr,
       case when grouping(term) = 0 then grade_pt
            else sum(case when rn = 1 then credit_hr * grade_pt end)
                                                          end as total_grade_pt,
       term
from   ( select g.emplid, g.course_id, c.credit_hr, g.grade_pt, g.term,
                row_number() over (partition by g.emplid, g.course_id 
                                   order by g.grade_pt desc) as rn
         from   grades g join credits c on g.course_id = c.course_id
       )
group by rollup(emplid, course_id, credit_hr, grade_pt, term)
having grouping(term) = 0 or (grouping(course_id) = 1 and grouping(emplid) = 0)
;

输出：

EMPLID  COURSE_ID  CREDIT_HR  TOTAL_GRADE_PT  TERM
------  ---------  ---------  --------------  ------
0001    6001               3               4  Fall15
0001    6002               3               3  Sp16
0001    6002               3             3.5  Fall15
0001    6003               3               2  Fall15
0001    6004               4             2.5  Sp16
0001                      13            38.5
0003    6002               3             3.5  Sp16
0003    6003               3             2.5  Sp15
0003    6003               3             2.5  Fall16
0003                       6              18