mysql - 查询 3 个表均未返回记录

Question

我需要查询 3 个表才能提取所有教师的平均成绩

我有下表:

学生

create table students

(id int not null auto_increment,
    surname varchar(100) not null,
    name varchar(100) not null,
    primary key (id),
    unique(nume,prenume)
  );

教师

create table teachers(
    id int not null auto_increment,
    surname varchar(100) not null,
    name varchar(100) not null,
    primary key (id),
    unique(nume,prenume)
    );

目录

create table catalog (
    id int not null auto_increment,
    `data` datetime not null,
    id_teacher int not null,
    id_student int not null,
    nota int not null,
    primary key (id),
    FOREIGN KEY (id_teacher) REFERENCES teachers(id),
    FOREIGN KEY (id_student) REFERENCES students(id)
  );

我需要进行查询，从中得出所有教师的平均学位 >7.5 的学生。

我尝试了这个，但没有结果，我不明白出了什么问题:

select avg(c.nota) AS 'medie', c.id_student, e.surname, e.name, c.id_teacher, p.surname, p.name from catalog c
left join students e on e.id = c.id_student
left join teachers p on p.id = c.id_teacher
group by c.id_teacher
having avg(c.nota) > 7.5;

Answer 1

使用此查询:

select s.id, avg(c.nota) medie
from catalog c
inner join students s on s.id = c.student_id
inner join teachers t on t.id = c.teacher_id
group by s.id, t.id

您将获得所有学生的 id，每位老师的平均值 > 7.5。
然后加入 students 表:

select s.id, s.surname, s.name 
from students s inner join (
  select s.id, avg(c.nota) medie
  from catalog c
  inner join students s on s.id = c.student_id
  inner join teachers t on t.id = c.teacher_id
  group by s.id, t.id
) g  ON g.id_student = s.id
group by s.id, s.surname, s.name 
having min(g.medie) > 7.5