前段時間面試的時候碰到這樣一個面試題，因為很久沒接觸sql竟然沒寫出來。

如圖有這樣一張成績表：

首先要理解group by 含義：“Group By”從字面意義上理解就是根據(jù)“By”指定的規(guī)則對數(shù)據(jù)進(jìn)行分組，所謂的分組就是將一個“數(shù)據(jù)集”劃分成若干個“小區(qū)域”，然后針對若干個“小區(qū)域”進(jìn)行數(shù)據(jù)處理。

先來看這樣一條sql語句：select  subject,max(score) from grade GROUP BY subject

結(jié)果是：

這樣正確的得出了每一科的最高分以及科目的名稱。那是不是再在后面加個name就可以得出對應(yīng)的學(xué)生的名字呢？我們可以試試：select  subject,max(score),name from grade GROUP BY subject

一看有結(jié)果以為成功了，但是對比數(shù)據(jù)后發(fā)現(xiàn)是錯的，學(xué)生姓名和分?jǐn)?shù)沒對應(yīng)上。如果你認(rèn)為是成功的是因為對group by理解的不夠，我也不知道這樣查詢?yōu)槭裁茨艹鰜斫Y(jié)果，我使用的mysql數(shù)據(jù)庫，如果是oracle的話就會報錯。

注意：因為在select指定的字段要么就要包含在Group By語句的后面，作為分組的依據(jù)；要么就要被包含在聚合函數(shù)中。

所以這樣是錯誤的。

group by語句中select指定的字段必須是“分組依據(jù)字段”，其他字段若想出現(xiàn)在select中則必須包含在聚合函數(shù)中，常見的聚合函數(shù)如下表：

函數(shù)	作用	支持性
sum(列名)	求和
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值
first(列名)	第一條記錄	僅Access支持
last(列名)	最后一條記錄	僅Access支持
count(列名)	統(tǒng)計記錄數(shù)	注意和count(*)的區(qū)別

我們還是分析要求，通過要求來寫sql語句。

這里提供幾種方法：

我們已經(jīng)通過group by分組來獲得每一科的最高分以及科目名稱，把它作為第一句sql，，然后再查詢一下score表，找到學(xué)科和分?jǐn)?shù)都相同的記錄:（子sql語句作為主sql語句的一部分）

#a.* 表示a表中所有的字段，b.*表示b表中所有的字段

select b.* from (select subject,max(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score

結(jié)果如下：

對比發(fā)現(xiàn)數(shù)據(jù)是對的。

拓展問法：用一句SQL查出所有課程成績最高和最低的學(xué)生及其分?jǐn)?shù)。

首先，通過分組獲得每個學(xué)科的最高分以及最低分：

select  subject,max(score),MIN(score) from grade GROUP BY subject

結(jié)果如下：

那我們?nèi)绾伟炎罡叻謱?yīng)的學(xué)生名字和最低分對應(yīng)的名字放入呢，而且要求的數(shù)據(jù)展示是最高分一行，最低分一行。所以這樣行不通。

通過上面的第一個問題得出的思路：

select b.* from (select subject,max(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score

這樣既然能得到每個學(xué)科的最高分，學(xué)生名字，學(xué)科名，那同樣把max（score）改成min（score）不就可以獲得最低分，學(xué)生名字，學(xué)科名字了嗎？現(xiàn)在重點是如何把兩條sql語句查詢出來的結(jié)果整合到一起。

select b.* from (select subject,min(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score

此時想到了sql的關(guān)鍵字 :    UNION的定義

UNION 操作符用于合并兩個或多個 SELECT 語句的結(jié)果集。

請注意，UNION 內(nèi)部的 SELECT 語句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型。同時，每條 SELECT 語句中的列的順序必須相同。注釋：默認(rèn)地，UNION 操作符選取不同的值。如果允許重復(fù)的值，請使用 UNION ALL。另外，UNION 結(jié)果集中的列名總是等于 UNION 中第一個 SELECT 語句中的列名。

所以得出的sql是這樣的：

select b.* from (select subject,max(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score UNION

select b.* from (select subject,min(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score

得出的結(jié)果是：

這樣就ok了。如果還想添加一些東西。例如添加一列說明這個分?jǐn)?shù)是最低分或者最高分。

select b.*,"最高分" from (select subject,max(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score 

UNION

select b.*,"最低分" from (select subject,min(score) m from grade GROUP BY subject) t,grade b where t.subject=b.subject and t.m=b.score