【面试题】
某线上学习平台设置学员线上学习阶梯，新学员购买50节课为一个学习阶段，学习完想要进入下个阶段必须再次购买，即续费（假设所有学员只能续费一次）并且每个学员可选择不同老师进行学习。
表一：学员上课表
表二：购买表
1.现求出续费学员在续费前3个月内的总课量，3个月给学员上课老师数量，以及每个上课老师给学员的上课量。
2.现求出每个续费学员在续费前的最后一节课的时间，以及对应的上课老师。
【解题步骤】
1.求出所有续费学员在续费前3个月内的总课量，3个月给学员上课老师数量，以及每个上课老师给学员的上课量。
1）所有续费学员在续费前3个月内的总课量
购买表中“订单类型”=2的为续费学员，上课信息在学员上课表中。要用到2个表，所以需要多表联结。
学员上课表和购买表，通过学生id作为联结条件。
使用哪种联结呢？
因为续费、上课属于两表的共同数据，所以使用内联结。下面得到续费学员的上课信息。
代码语言：javascript复制1 select *
2 from 学员上课表 as a
3 inner join 购买表 as b
4 on a.学员id=b.学员id
5 where b.订单类型=2;查询结果
如何求得续费前3个月的课程量？
这涉及到计算两个日期之间的差值，《猴子 从零学会sql》里讲到对应的函数是timestampdiff。下图是这个函数的用法。
用case语句来判断，新增一列为“续费前3个月数”。如果(上课时间-续费时间)<=3，那么“续费前3个月数”列中对应的值标记为1。否则标记为null。
代码语言：javascript复制1 select *,
2 (case when
3 timestampdiff(month,a.上课时间,b.续费时间)<=3
4 then 1
5 else null
6 end) as 续费前三个月数
7 from 学员上课表 as a
8 inner join 购买表 as b
9 on a.学员id
10 =b.学员idwhere b.订单类型=2;查询结果
对续费前3个月的记录进行计数（count）得出所有续费学员的总课量，对老师id 去重计数得出上课老师数量。
代码语言：javascript复制1 select count(distinct a.老师id) as 上课老师数量,
2 count(case when
3 timestampdiff(month,a.上课时间,b.续费时间)<=3
4 then 1
5 else null end) as 续费前三个月数
6 from 学员上课表 as a
7 inner join 购买表 as b
8 on a.学员id=b.学员id
9 where b.订单类型=2;查询结果如下：
2）每个上课老师给学员的上课量。
当有“每个”出现的时候，要想到《猴子 从零学会SQL》中讲过的用“分组汇总来”来实现。
按老师id分组（group by ），汇总续费前三个月课程量（计数函count）。
代码语言：javascript复制1 select a.老师id,
2 count(case when
3 timestampdiff(month,a.上课时间,b.续费时间)<=3
4 then 1
5 else null end) as 续费前三个月数
6 from 学员上课表 as a
7 inner join 购买表 as b
8 on a.学员id=b.学员id
9 where b.订单类型=2
10 group by a.老师id;查询结果：
2.求出每个续费学员在续费前的最后一节课的时间，以及对应的上课老师。
1）跟前面题目一样，找出购买表中“订单类型”=2的为续费学员。两表联结获得续费学员的上课信息。
1 select *
2 from 学员上课表 as a
3 inner join 购买表 as b
4 on a.学员id=b.学员id
5 where b.订单类型=2;筛选早于续费日期的学习记录
代码语言：javascript复制1 select *
2 from 学员上课表 as a
3 inner join 购买表 as b
4 on a.学员id=b.学员id
5 where b.订单类型=2
6 and a.上课时间 < b.续费时间;查询结果如下：
2）题目要求查询“每个用户”，当每个出现的时候，就要想到分组汇总（group by或者窗口函数的partiotion by）。
3）续费前最后一节课的时间
大白话翻译就是，上课时间最晚的课程信息。按照上课时间对每个学员id 的上课记录进行排名，然后取出最后一条课程数据就是。
又涉及到分组，又涉及到排名的问题，要想到用《猴子 从零学会SQL》里讲过的窗口函数来实现。
所以使用分组（窗口函数partiotion by学员id），并按最后交易时间降序排列(order by上课时间 desc)，套入窗口函数的语法，得出下面的sql语句：
代码语言：javascript复制1 select a.*,b.续费时间,
2 row_number() over (
3 partition by a.学员id
4 order by a.上课时间 DESC ) as 上课时间排序
5 from 学员上课表 as a
6 inner join 购买表 as b
7 on a.学员id=b.学员id
8 where b.订单类型=2
9 and a.上课时间 < b.续费时间;查询结果：
3）可以看到通过上课时间降序排序后，最晚的学习的记录是排在第一条，用where 筛选出每个学员的第1条记录，得出每个学员续费前的学习记录。
代码语言：javascript复制1 select * from
2 (select a.*,b.续费时间,
3 row_number() over (
4 partition by a.学员id
5 order by a.上课时间 DESC ) as 上课时间排序
6 from 学员上课表 as a
7 inner join 购买表 as b
8 on a.学员id=b.学员id
9 where b.订单类型=2
10 and a.上课时间 < b.续费时间) as c
11 where 上课时间排序=1;查询结果：
【本题考点】
1.条件判断，需要灵活使用case。
2.多表联结的使用，学会判断使用哪种联结。
3.熟悉时间差函数的用法，方便计算多少时间间隔内的数据。
4.遇到对每个进行排名问题，首先要想到使用窗口函数来实现。
