【面試題】

有一張“學(xué)生成績表”，包含4個(gè)字段：班級id、學(xué)生id、課程id、成績。

問題1：求出每個(gè)學(xué)生成績最高的三條記錄

問題2：找出每門課程都高于班級課程平均分的學(xué)生

【解題步驟】

1. topN問題

問題1是常見的排名問題（topN問題），要想到用《猴子從零學(xué)會SQL》里講過的窗口函數(shù)來解決這類業(yè)務(wù)問題。

用窗口函數(shù)獲取順序有三種：rank()，dense_rank()和row_number()。

同樣是按“值”從小到大排序，三者的區(qū)別如下：

根據(jù)問題的描述，我們應(yīng)該使用dense_rank窗戶函數(shù)

查詢結(jié)果：

2. 匯總分析

問題2要求找出每門課程都高于班級課程平均分的學(xué)生，可以拆解成以下幾個(gè)問題：

1）求出每個(gè)班級，每門課程的平均分

2）將學(xué)生每門課程的成績與所在班級的對應(yīng)課程平均分相減，結(jié)果大于0就說明該學(xué)生的這門成績高于課程平均分

3）“找出每門課程都高于班級課程平均分的學(xué)生”說明對于學(xué)生來說，最小的“相減結(jié)果”都是大于0的

首先用匯總分析求出每個(gè)班級，每門課程的平均分。

查詢結(jié)果：

3. 多表聯(lián)結(jié)

涉及到多表查詢，需要用到多表聯(lián)結(jié)。

這里目的是為了將“將學(xué)生每門課程的成績與所在班級的對應(yīng)課程平均分相減”。

所以，是將原始的“學(xué)生成績表”與“班級的課程平均分”進(jìn)行聯(lián)結(jié)。

為了保持左表“學(xué)生成績表”的所有數(shù)據(jù)，將所有學(xué)生的成績都與“課程平均分”x相減，所以選擇“左聯(lián)結(jié)（left join）”。

最后使用分組匯總，并結(jié)合having條件篩選出“相減結(jié)果的最小值大于0”的學(xué)生。

【本題考點(diǎn)】

1.考查對分組匯總的了解，以及靈活使用來解決業(yè)務(wù)問題；

2.考查對多表聯(lián)結(jié)的了解，以及靈活使用來解決業(yè)務(wù)問題；

3）考查對窗口函數(shù)的了解，窗戶函數(shù)解決的經(jīng)典問題就那么幾種，記下來，就可以解決99%的業(yè)務(wù)問題。