主页 / 问题 / 1471529
Accepted
Rasmus_Rei
Asked:2022-11-27 10:45:52 +0000 UTC2022-11-27 10:45:52 +0000 UTC

为每个学生找到作业 GPA 最好的科目

  • 772

有一个任务表。

STUDENT_ID COURSE_ID DATE        TASK_NR FILE_NAME                POINTS    TASK_ID
---------- --------- -------- ---------- -------------------- ---------- ----------
10020011   I319      17.06.03          1 t3.txt                        8          3
10020014   I319      01.06.03          1 t7.txt                       10          4
10020015   I319      11.07.03          1 t8.txt                       10          5
10020011   I319      11.06.04          2 t2.txt                        7          6
10020015   I319      11.06.04          3 t2.txt                        6          7
10020013   I319      11.06.04          3 t3.txt                        8          8
10020011   Iad307    11.06.03          1 t1.txt                        5          9
10020011   Id310     12.06.03          1 t2.txt                       7         10
10020011   ID218     17.06.03          1 t3.txt                        8         11
10020014   Iad307    01.06.03          1 t7.txt                       10         12
10020015   Iad307    11.07.03          1 t8.txt                       10         13

任务的本质是从TASKS表中得到每个学生在哪个科目中他的任务平均分是最好的。

应该看起来像这样

STUDENT_ID COURSE_ID        AVG
---------- --------- ----------
10020011   ID218              8
10020013   ID218            9,5
10020014   I319              10
10020015   Iad307            10

首先，我找到每个学生每个科目的平均分数

SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id 
ORDER BY student_id;

STUDENT_ID COURSE_ID        AVG
---------- --------- ----------
10020011   Id310              7
10020011   Iad307          5,75
10020011   I319               5
10020011   ID218              8
10020013   ID218            9,5
10020013   I319               8
10020014   I319              10
10020014   Iad307          6,25
10020014   ID218              6
10020015   Id309            5,5
10020015   I319               8

接下来，我想获取每个学生的学生id、课程id和这门课程的最高分

SELECT student_id, course_id, MAX(avg) AS max FROM (
SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id ORDER BY student_id;

我得到一个错误

ORA-00979: 表达式不是 GROUP BY 表达式 00979。00000 - “不是 GROUP BY 表达式”

尝试了变体

SELECT student_id, ANY_VALUE(course_id), MAX(avg) AS max FROM (
SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id ORDER BY student_id;

我得到一个错误

ORA-00904: “ANU_VALUE”：无效的标识符 00904。00000 - “%s：无效的标识符”

是否有 ANY_VALUE 的替代方案或另一种解决问题的方法？

sql

1 个回答

  1. Best Answer

    试图继续玩http://sqlfiddle.com/。
    问题的本质是从字段中选择一个值，该值course_id对应于平均值中的最大分数。RANK()可以使用 1)和 2)以两种方式解决三层 SELECT 问题row_number()。在我看来，选项 #1 比 #2 更正确，因为如果最大分数相等，它会显示同一学生的所有结果。初始数据（构建架构）：

    CREATE TABLE tasks
    ("STUDENT_ID" int, "COURSE_ID" varchar2(6), "DATE" varchar2(8), "TASK_NR" int, "FILE_NAME" varchar2(6), "POINTS" int, "TASK_ID" int)
;

INSERT ALL 
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020011, 'I319', '17.06.03', 1, 't3.txt', 8, 3)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020014, 'I319', '01.06.03', 1, 't7.txt', 10, 4)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020015, 'I319', '11.07.03', 1, 't8.txt', 10, 5)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020011, 'I319', '11.06.04', 2, 't2.txt', 7, 6)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020015, 'I319', '11.06.04', 3, 't2.txt', 6, 7)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020013, 'I319', '11.06.04', 3, 't3.txt', 8, 8)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020011, 'Iad307', '11.06.03', 1, 't1.txt', 5, 9)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020011, 'Id310', '12.06.03', 1, 't2.txt', 7, 10)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020011, 'ID218', '17.06.03', 1, 't3.txt', 8, 11)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020014, 'Iad307', '01.06.03', 1, 't7.txt', 10, 12)
    INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
         VALUES (10020015, 'Iad307', '11.07.03', 1, 't8.txt', 10, 13)
SELECT * FROM dual
;

    RANK()SQL 查询 No. 1 - 首先我们按照原始代码对分数进行平均，然后我们使用（排名 1 为最大值）以相反的顺序对它们进行排名，然后我们选择排名值 = 1 的记录。这可能会导致多个值对于同一个 same student_id，如果不同课程的最高平均分相等：

    SELECT student_id, course_id,  sred FROM (
SELECT student_id, course_id,  sred, RANK() OVER (PARTITION BY student_id ORDER BY sred DESC) as rank_sred FROM (
SELECT student_id, course_id, AVG(points) AS sred FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id,course_id, sred ORDER BY student_id) WHERE RANK_SRED=1;

    结果 1（14 号学生在两门课程中获得 10 分）：

    STUDENT_ID  COURSE_ID  SRED
10020011    ID218       8
10020013    I319        8
10020014    I319       10
10020014    Iad307     10
10020015    Iad307     10

    在此处输入图像描述

    第二种方式 - 通过row_number()- 仅选择组中的第一个结果：

    SELECT student_id, course_id,  sred FROM (
SELECT student_id, course_id,  sred, row_number() over (partition by student_id order by sred DESC) as row_n FROM (
SELECT student_id, course_id, AVG(points) AS sred FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id,course_id, sred ORDER BY student_id) WHERE row_n=1;

    结果 #2（学生 #14 在一份副本中）

    STUDENT_ID  COURSE_ID   SRED
10020011    ID218        8
10020013    I319         8
10020014    I319        10
10020015    Iad307      10

    了解正确的结果——在 Excel 中解析的数据

    在此处输入图像描述

    • 2

相关问题