sql - GROUP BY的局限性

Question

免责声明:我是SQL newb，适用于一个类，但我确实可以按正确的方向使用戳戳。

我有以下三个表:
student(_sid_, sname, sex, age, year, gpa)section(_dname_, _cno_, _sectno_, pname)enroll(_sid_, grade, _dname_, _cno_, _sectno_)(下划线表示的主键)

我正在尝试编写一个与Oracle兼容的SQL查询，该查询返回一个表，该表的学生姓名(student.sname)在每个部分中都有最高的gpa(其中包括section.cno和section.sectno)以及section的所有其他属性。

我设法使用汇总查询和GROUP BY来获取每个部分的最大GPA:

  SELECT MAX(s.gpa), e.cno, e.sectno  
    FROM enroll e, 
         student s  
   WHERE s.sid = e.sid  
GROUP BY e.cno, e.sectno

更不用说其他 section属性了，我什至无法弄清楚如何添加学生姓名( student.sname)。如果我将其添加到 SELECT子句中，则必须将其包含在 GROUP BY中，这会弄乱查询的其余部分。如果我在外部查询的 WHERE或 FROM子句中使用整个查询，那么我只能访问表中的三个字段，用处不大。

我知道您无法给我确切的答案，但是任何提示将不胜感激!

Answer 1

假设使用Oracle 9i +，要让只有一名GPA最高的学生(如果出现平局)，请使用:

WITH summary AS (
   SELECT e.*,
          s.name,
          ROW_NUMBER() OVER(PARTITION BY e.cno, e.sectno
                                ORDER BY s.gpa DESC) AS rank
     FROM ENROLL e
     JOIN STUDENT s ON s.sid = e.sid)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1

非CTE等效:

SELECT s.*
  FROM (SELECT e.*,
               s.name,
               ROW_NUMBER() OVER(PARTITION BY e.cno, e.sectno
                                     ORDER BY s.gpa DESC) AS rank
          FROM ENROLL e
          JOIN STUDENT s ON s.sid = e.sid) s
 WHERE s.rank = 1

如果要查看所有获得GPA并列的学生，请使用:

WITH summary AS (
   SELECT e.*,
          s.name,
          DENSE_RANK OVER(PARTITION BY e.cno, e.sectno
                              ORDER BY s.gpa DESC) AS rank
     FROM ENROLL e
     JOIN STUDENT s ON s.sid = e.sid)
SELECT s.*
  FROM summary s
 WHERE s.rank = 1