时间戳

首页 > 解决方案 > 具有特定约束的 MySQL 查询？

mysql - 具有特定约束的 MySQL 查询？

问题描述

在创建以下数据库后，我正在尝试创建 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;

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

标签： mysqlsql

解决方案

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

在您回答“每个老师有多少学生”问题时，您缺少一个关键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

推荐阅读