【面試題】
某線上學習平台設定學員線上學習階梯，新學員購買50節課為一個學習階段，學習完想要進入下個階段必須再次購買，即續費（假設所有學員隻能續費一次）并且每個學員可選擇不同老師進行學習。
1.現求出續費學員在續費前3個月内的總課量，3個月給學員上課老師數量，以及每個上課老師給學員的上課量。
2.現求出每個續費學員在續費前的最後一節課的時間，以及對應的上課老師。
【解題步驟】
1.求出所有續費學員在續費前3個月内的總課量，3個月給學員上課老師數量，以及每個上課老師給學員的上課量。
1）所有續費學員在續費前3個月内的總課量
購買表中“訂單類型”=2的為續費學員，上課資訊在學員上課表中。要用到2個表，是以需要多表聯結。
學員上課表和購買表，通過學生id作為聯結條件。
使用哪種聯結呢？
因為續費、上課屬于兩表的共同資料，是以使用内聯結。下面得到續費學員的上課資訊。
select *
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2; 用case語句來判斷，新增一列為“續費前3個月數”。如果(上課時間-續費時間)<=3，那麼“續費前3個月數”列中對應的值标記為1。否則标記為null。
select *,
(case when
timestampdiff(month,a.上課時間,b.續費時間)<=3
then 1
else null
end) as 續費前三個月數
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2; 對續費前3個月的記錄進行計數（count）得出所有續費學員的總課量，對老師id 去重計數得出上課老師數量。
條件計數用case when
select count(distinct a.老師id) as 上課老師數量,
count(case when
timestampdiff(month,a.上課時間,b.續費時間)<=3
then 1
else null end) as 續費前三個月數
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2; 2）每個上課老師給學員的上課量。
當有“每個”出現的時候，要想到《猴子 從零學會SQL》中講過的用“分組彙總來”來實作。
按老師id分組（group by ），彙總續費前三個月課程量（計數函count）。
select a.老師id,
count(case when
timestampdiff(month,a.上課時間,b.續費時間)<=3
then 1
else null end) as 續費前三個月數
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2
group by a.老師id; 2.求出每個續費學員在續費前的最後一節課的時間，以及對應的上課老師。
1）跟前面題目一樣，找出購買表中“訂單類型”=2的為續費學員。兩表聯結獲得續費學員的上課資訊。
select *
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2; 篩選早于續費日期的學習記錄
select *
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2
and a.上課時間 < b.續費時間; 2）題目要求查詢“每個使用者”，當每個出現的時候，就要想到分組彙總（group by或者視窗函數的partiotion by）。
3）續費前最後一節課的時間
大白話翻譯就是，上課時間最晚的課程資訊。按照上課時間對每個學員id 的上課記錄進行排名，然後取出最後一條課程資料就是。
又涉及到分組，又涉及到排名的問題，要想到用《猴子 從零學會SQL》裡講過的視窗函數來實作。
是以使用分組（視窗函數partiotion by學員id），并按最後交易時間降序排列(order by上課時間 desc)，套入視窗函數的文法，得出下面的sql語句：
select a.*,b.續費時間,
row_number() over (
partition by a.學員id
order by a.上課時間 DESC ) as 上課時間排序
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2
and a.上課時間 < b.續費時間; 3）可以看到通過上課時間降序排序後，最晚的學習的記錄是排在第一條，用where 篩選出每個學員的第1條記錄，得出每個學員續費前的學習記錄。
select * from
(select a.*,b.續費時間,
row_number() over (
partition by a.學員id
order by a.上課時間 DESC ) as 上課時間排序
from 學員上課表 as a
inner join 購買表 as b
on a.學員id=b.學員id
where b.訂單類型=2
and a.上課時間 < b.續費時間) as c
where 上課時間排序=1; 【本題考點】
1.條件判斷，需要靈活使用case。
2.多表聯結的使用，學會判斷使用哪種聯結。
3.熟悉時間差函數的用法，友善計算多少時間間隔内的資料。