具有特定约束的 MySQL 查询？

Question

在创建以下数据库后，我正在尝试创建 MySQL 查询来回答一些问题:

create table teachers (
    teacher_id int primary key auto_increment,
    name varchar(50),
    surname varchar(50),
    dob date,
    employment_day date
);

create table students (
    student_id int primary key auto_increment,
    name varchar(50),
    surname varchar(50),
    dob date,
    no_of_lessons_left int
);

create table aircrafts (
    aircraft_id int primary key auto_increment,
    manufacturer varchar(50),
    serial_number varchar(50),  -- or int?
    cycles int
);

create table schools (
    school_id int primary key auto_increment,
    address varchar(50),
    opening_date date
);

create table lessons (
    lesson_id int primary key auto_increment,
    teacher_id int not null,
    student_id int not null,
    aircraft_id int not null,
    school_id int not null,
    lesson_date date,
    constraint lessons_teacher foreign key(teacher_id) references teachers(teacher_id),
    constraint lessons_student foreign key(student_id) references students(student_id),
    constraint lessons_aircraft foreign key(aircraft_id) references aircrafts(aircraft_id),
    constraint lessons_school foreign key(school_id) references schools(school_id)
);

在一些查询下面，我尝试使用双连接解决问题:
问题是:
• 在某个时间点，每所学校有多少架飞机？
我不知道如何回答这些条件“ 每个 学校 在某个时间 时间”
• 一所特定学校有多少学生？

select count(*) from students s 
inner join lessons l on  l.student_id = s.student_id 
inner join schools sc sc.teacher_id= l.teacher_id
where sc.name = "PilotingSchoolName";

• 每个老师在某个特定时刻有多少学生

select count(*) from students s 
inner join lessons l on  l.student_id = s.student_id 
inner join schools sc sc sc.teacher_id= l.teacher_id;

但是，我在我写的查询中缺少条件“ 在某个特定时刻 ”。

Answer 1

您有一个 lessons.lesson_date列，并且可以使用 AND lesson_date = '2019-12-08' 之类的内容限制为一个时间点或日期范围 AND lesson_date BETWEEN '2018-01-01' AND '2019-12-08' .如果要求没有要求您找到具体的确切日期，听起来他们只是希望您能够演示如何在 WHERE 中使用类(class)日期。 .

在您对“每个老师有多少学生”问题的回答中，您遗漏了一个关键的 GROUP BY条款。您现在的查询很接近，但会显示注册学生的总数。按 teachers.name 分组将完成查询(以及 lesson_date 约束)

select
  t.name,
  -- count each student only once per teacher
  count(DISTINCT s.student_id) AS 
from teachers t
  -- left join so teachers with no enrolled
  -- students will list as zero
  left join lessons l on t.teacher_id = l.teacher_id 
  left join students s ON l.student_id = s.student_id
WHERE
  -- A specific date
  l.lesson_date = '2019-12-08'
GROUP BY t.name

要在特定时间获取每所学校的飞机，查询非常相似，但连接不同。

select
  -- List all schools, and count of aircraft per school
  s.school_id,
  count(DISTINCT l.aircraft_id)
from
  schools s
  -- left join, so that schools with zero current aircraft
  -- are include in the results
  left join lessons l ON l.school_id = s.school_id
where
  l.lesson_date = '2019-12-08'
-- GROUP BY necessary to get the count per school
GROUP BY s.school_id