在一个 SQL 查询中返回许多不同类别的数据中的每一个的最新值？

Question

现在我有一个查询返回多个数据“类”的最大值。我正在尝试找出要更改的内容，以便我的查询将为下面每个不同的类返回最新的最新值。

数据本身是一个学生成绩的大表。学生可以有多个学期的成绩（由 pgf.finalgradename 字段捕获）。对于大多数学生，只需通过学生标识符和班级标识符加入即可返回正确的数据；但是，我试图考虑学生放弃课程的一个部分并注册另一部分（同一课程）的情况。出于我的目的，我们可以假设最近更新的成绩代表首选记录。

除了下面的字段之外，还有一个 pgf.lastgradeupdate 字段，表示更新记录的日期。在英语中，我希望返回的是基于 pgf.lastgradeupdate 的最新行。给定这些数据：

studentid     course_number     finalgradename     percent     lastgradeupdate
100           M900              H1                 70          01-OCT-10
100           M900              H1                 90          20-OCT-10
100           M900              H2                 85          01-JAN-11
100           M900              H3                 88          06-FEB-11
100           M900              H4                 89          07-JUN-11

我希望查询返回：

studentid     H1     H2     H3     H4
100           90     85     88     89

这就是我所拥有的：

select cc.studentid, 
max(case when pgf.finalgradename='H1' then pgf.percent else null end) as H1,
max(case when pgf.finalgradename='H2' then pgf.percent else null end) as H2,
max(case when pgf.finalgradename='H3' then pgf.percent else null end) as H3,
max(case when pgf.finalgradename='H4' then pgf.percent else null end) as H4
from cc

left outer join sections sect on abs(cc.sectionid) = sect.id
left outer join courses on sect.course_number = courses.course_number
left outer join pgfinalgrades pgf on cc.studentid = pgf.studentid and abs(cc.sectionid) = pgf.sectionid

where abs(cc.termid) >= 2000 and cc.course_number = 'M900'
group by cc.studentid

我是否需要为每个数据“类”创建一个子查询？或者我需要做一堆自连接吗？我在这里读到的其他问题似乎只是基于只需要考虑一个日期而不是多个日期的情况。 谢谢！

Answer 1

这离你想要的有多近？

create table your_data(
  studentid       number
 ,course_number   varchar2(4)
 ,finalgradename  varchar2(2)
 ,percent         number
 ,lastgradeupdate date
);

insert into your_data values(100, 'M900', 'H1', 70, date '2010-10-01');
insert into your_data values(100, 'M900', 'H1', 90, date '2010-10-20');
insert into your_data values(100, 'M900', 'H2', 85, date '2011-01-01');
insert into your_data values(100, 'M900', 'H3', 88, date '2011-02-06');
insert into your_data values(100, 'M900', 'H4', 89, date '2011-06-07');

commit;


select studentid
      ,course_number
      ,max(case when finalgradename = 'H1' then percent end) as h1
      ,max(case when finalgradename = 'H2' then percent end) as h2
      ,max(case when finalgradename = 'H3' then percent end) as h3
      ,max(case when finalgradename = 'H4' then percent end) as h4
  from (select studentid
              ,finalgradename
              ,course_number
              ,percent
              ,row_number() over(partition by studentid
                                             ,course_number
                                             ,finalgradename 
                                     order by lastgradeupdate desc) as rn
          from your_data
        )
 where rn = 1
 group 
    by studentid
      ,course_number;

 STUDENTID COUR         H1         H2         H3         H4
---------- ---- ---------- ---------- ---------- ----------
       100 M900         90         85         88         89

更新的查询：
我将您的查询插入到我的查询中。但我无法测试它......让我知道它是否有效！

select studentid
      ,course_number
      ,max(case when finalgradename = 'H1' then percent end) as h1
      ,max(case when finalgradename = 'H2' then percent end) as h2
      ,max(case when finalgradename = 'H3' then percent end) as h3
      ,max(case when finalgradename = 'H4' then percent end) as h4
  from (select cc.studentid
              ,pgf.finalgradename
              ,courses.course_number
              ,pgf.percent
              ,row_number() over(partition by cc.studentid
                                             ,courses.course_number
                                             ,pgf.finalgradename 
                                     order by lastgradeupdate desc) as rn
          from cc
    left outer 
          join sections sect     on abs(cc.sectionid) = sect.id
    left outer 
          join courses           on sect.course_number = courses.course_number
    left outer 
          join pgfinalgrades pgf on (cc.studentid = pgf.studentid and 
                             abs(cc.sectionid) = pgf.sectionid)
         where abs(cc.termid) >= 2000 and cc.course_number = 'M900'
        )
 where rn = 1
 group 
    by studentid
      ,course_number;