各专业平均成绩较高的学生 [英] Student with greater average grades by major

问题描述

表和数据:

CREATE TABLE major
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200)
);

insert into major values 
    (101, 'Computing'), 
    (102, 'Arquitecture');

CREATE TABLE student
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200), 
    major_id INT,
    foreign key(major_id) references major(id)
);

insert into student  values
    (1001, 'Claude', 101),
    (1002, 'John', 101),
    (1003, 'Peter', 102);

CREATE TABLE course
( 
    id INT PRIMARY KEY, 
    name VARCHAR(200)
);

insert into course values 
    (901, 'Databases'), 
    (902, 'Java'),
    (903, 'Artificial Intelligence'), 
    (904, 'OOP');
    
CREATE TABLE grades
(
   student_id INT,
   course_id INT,
   grade integer,
   primary key (student_id, course_id),
   foreign key(student_id) references student(id),
   foreign key(course_id) references course(id)
);

insert into grades  values
    (1001, 903, 95),
    (1001, 904, 88),
    (1002, 901, 76),
    (1002, 903, 82),
    (1003, 902, 87);

预期:

| student    | major        | grade |
| ---------- | -------------| ----- |
| Peter      | Architecture | 87    |
| Claude     | Computing    | 91.5  |

换句话说:检索每个专业的高年级学生.

In other words: retrieve top grade student for each major.

游乐场此处.

如果可能，没有TOP，LIMIT.

If possible, without TOP,LIMIT.

如果可能的话，请使用旧版ANSI SQL以及使用窗口函数.

If possible old ANSI SQL as well as using window functions.

使用MySQL引擎，但不是必需的.

Engine MySQL, but not required.

我的方法#1 :

-- average grade by student
select s.name as Student,  m.name as Major, avg(g.grade) as Average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id

但是不需要约翰:

| Student | Major        | Average |
| ------- | ------------ | ------- |
| Claude  | Computing    | 91.5000 |
| John    | Computing    | 79.0000 |
| Peter   | Arquitecture | 87.0000 |

我的方法#2 :

-- Max average grade by career; lacks student
select a.major, max (a.average) as Average
    from (select s.name as Student,  m.name as Major, avg(g.grade) as average
         from student s
         inner join grades g on (s.id = g.student_id)
         inner join major m on (m.id = s.major_id)
         group by s.id) a
    group by a.major;           

但缺少学生专栏.

| major        | Average |
| ------------ | ------- |
| Arquitecture | 87.0000 |
| Computing    | 91.5000 |

谢谢.

推荐答案

如果运行的是MySQL 8.0，则可以使用rank()进行此操作:

If you are running MySQL 8.0, you can do this with rank():

select *
from (
    select s.name as student, m.name as major, avg(g.grade) as average,
        rank() over(partition by m.id order by avg(g.grade) desc) rn
    from student s
    inner join grades g on s.id = g.student_id
    inner join major m on  m.id = s.major_id
    group by s.id, m.id
) t
where rn = 1

注意:

• rank()允许联系

括号是多余的

这篇关于各专业平均成绩较高的学生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！