MySQL学习笔记（一）

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 4 天，点击查看活动详情

最近刷到lintCode上比较全面的sql题，对新手学习来说能够很好的读懂sql的原理。

情况是这样的，现有 teachers 表中记录着离职的教师信息，id 的值即是教师离职的顺序，在 courses 表中记录着每个教师教授的课程信息，关联 id 为 teacher_id。现在知道教师是以国籍来分团队的，且每个团队的成立至少有 3 个教师，若是一个教师团队集体离职，则在 teachers 表中信息记录为连续且国籍相同，现在需要统计每一个教师团队集体离职会有多少学生需要等待新的教师，结果按团队国籍排序。

首先读懂几个关键的信息

• 有teachers和courses两张表，id和teacher_id是关联关系
• 教师按国籍分团队，团队的人数不少于3个人
• 计算每个教师团队教授学生的总数
• 一个教师团队集体离职在teachers表中展示为连续3个或以上并且国籍相同的记录，如果teachers表记录中连续且国籍相同的教师人数小于3则不记录进统计表里面。

表teachers:

列名	类型	注释
id	int	主键，教师离职的顺序
name	verchar	讲师姓名
email	verchar	邮箱
age	int	年龄
country	verchar	国籍

表courses:

列名	类型	注释
id	int	主键
name	verchar	课程名称
student_count	int	学生总数
created_at	date	课程创建时间
teacher_id	int	讲师id

最终得到的视图：

列名	类型	注释
country	verchar	国籍
student_count	int	学生总数

获取连续相同国籍的教师记录

怎样算连续呢？根据教师离职的顺序当前行加一等于下一行，或者当前行减一等于上一行，则为连续数字。distinct关键字让id是唯一的。

select distinct t1.id, t1.country from teachers as t1 left join 
teachers as t2 on t1.id = t2.id - 1 or t1.id = t2.id + 1 
where t1.country = t2.country

连续且国籍相同的记录条数需要大于2，所以采用having count计算相同国籍的条数

...
group by t1.country having count(*)>2
...

联表获取教师所有课程下的学生总数

与courses表联表查询教师所有的课程，并且用sum函数聚合教师的所有课程的学生总和。

...left join 
(select teacher_id, sum(student_count) as sums from courses group by teacher_id) as c
on t1.id = c.teacher_id
...

将符合条件的教师团队的学生总数记录下来

结合上面的条件，得到最终的sql请求：

select t.country, sum(t.sums) student_count from 
(
    select distinct t1.id, t1.country, c.sums from teachers as t1 
    left join 
    teachers as t2 on t1.id = t2.id - 1 or t1.id = t2.id + 1 
    left join 
    (select teacher_id, sum(student_count) as sums from courses group by teacher_id) as c
    on t1.id = c.teacher_id
    where t1.country = t2.country
) as t
group by t.country having count(*)>2 order by country desc;

Ending~