在面試的時(shí)候我經(jīng)常會(huì)給畢業(yè)生出這樣一道SQL題：

有一張表studentgrade，包含三個(gè)字段name,subject和grade，如下所示：

name subject grade
a    數(shù)學(xué)    99
b    英語(yǔ)    67
a    英語(yǔ)    77

題目要求應(yīng)試者寫(xiě)出sql，查詢(xún)每個(gè)人的最高分和最高分的科目，比如

a   數(shù)學(xué)  99

下面分析這個(gè)SQL的查詢(xún)方法，分組函數(shù)是最常使用的，下面的分組函數(shù)可以得到最高分，姓名
select name,max(grade) from studentgrade group by name;

這樣顯然得不到具體的科目，要得到科目怎么辦呢？使用臨時(shí)表就是一個(gè)方案：

select b.name,b.grade,b.subject from

(select name, max(grade) grade from studentgrade group by name ) a,

studentgrade b

where a.name = b.name and a.grade = b.grade；

如果是需要查詢(xún)每個(gè)人前三個(gè)最高分的成績(jī)和科目呢？分組函數(shù)ms就無(wú)能為力了。下面是是幾個(gè)不錯(cuò)的方式：

1、對(duì)每條記錄進(jìn)行檢驗(yàn)，查詢(xún)這條記錄是不是排在前三位的

select a.* from studentgrade a where 3 > (select count(*) from studentgrade where name=a.name and grade< a.grade) order by a.name,a.grade;

為提高查詢(xún)效率，在name和grade上面建立聯(lián)合索引。

2、先把每個(gè)同學(xué)最大的三個(gè)科目分?jǐn)?shù)查詢(xún)出來(lái)形成一個(gè)集合，然后對(duì)每條記錄進(jìn)行檢驗(yàn)，查看是否在這個(gè)集合里面。這種方式是mysql特有的，使用了top關(guān)鍵字。

select a.* from studentgrade a where grade in (select top 3 grade from studentgrade where name=a.name order by grade) order by a.name,a.grade;

3、和第一種方式差不多，但使用的exists關(guān)鍵字。

select a.* from studentgrade a where exists (select count(*) from studentgrade where name=a.name and grade<a.grade having Count(*) < 2) order by a.name;