1、問題

現(xiàn)在有數(shù)據(jù)表，存儲著各個學生的各個科目的成績。如果查出每門課程得分最高的學生該怎么找呢？
正常的思路可能是先按分組查出每個科目的最高成績，然后再將這個結(jié)果和原表做一個連接查詢(按照科目和成績相等連接)，這樣才可以查出想要的結(jié)果?；蛘呤?，多寫幾個子查詢，應該也能解決問題。
但是，這些答案都不好，沒有充分發(fā)揮SQL語法中關(guān)系代數(shù)的優(yōu)勢。本文主要講如何通過自連接，解決這個問題。

2、準備

2.1 表結(jié)構(gòu)

建立如下表結(jié)構(gòu)：

create table Grade (
    StudentName varchar2(50),
    CourseName varchar2(100),
    Grade number
);

這里省略了主鍵和索引等。

2.2 數(shù)據(jù)

執(zhí)行如下腳本，向表中插入數(shù)據(jù)：

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('John Smith', 'English', 76);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('John Wall', 'Math', 86);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Kobe Byrant', 'Math', 90);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Will Smith', 'English', 96);

INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Li Lei', 'Math', 99);


INSERT INTO grade (StudentName, CourseName, Grade)
VALUES ('Tim Duncan', 'English', 95);

COMMIT;

數(shù)據(jù)如下：

3、查詢語句

3.1 思路

這里的解決方案是做一個自連接(連接條件：t.CourseName = tt.CourseName AND tt.grade >= t.grade)，查找比當前記錄分數(shù)高的記錄只有一條的記錄(這一條自然就是記錄本身了)。自連接之后的篩選方法是，先按照t.StudentName, t.CourseName分組，然后，篩選出只有一條記錄的組就好了。

3.2 自連接SQL v1.0

根據(jù)上面的思路，可以寫出如下的SQL：

SELECT t.StudentName,
       t.CourseName
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName
HAVING count(1) <= 1

結(jié)果如下：

3.3 自連接SQL v2.0

前面的結(jié)果中，沒法看到各個科目成績最高的學生的分數(shù)，因為分數(shù)字段沒有出現(xiàn)在group by表達式中。
這個非常好解決，不會有相同StudentName和CourseName，但是成績不同的情況，所以，直接在group by子句中加上這個字段就行。如下。

SELECT t.CourseName,
       t.StudentName,
       t.grade
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName,
         t.grade
HAVING count(1) <= 1

查詢結(jié)果如下：

3.4 查詢各個科目的成績排名

根據(jù)這個原理，可以很容易地查詢各個科目的成績排名。
自連接之后，有多少條比當前記錄大的(實際是大于等于)，就認為排名是第幾。

SELECT t.CourseName,
       t.StudentName,
       t.grade,
       count(1) CourseRank
FROM Grade t,
     Grade tt
WHERE t.CourseName = tt.CourseName
  AND tt.grade >= t.grade
GROUP BY t.StudentName,
         t.CourseName,
         t.grade
order by t.CourseName, CourseRank

結(jié)果如下：

3.5 Oracle中partition by語句的實現(xiàn)

利用oracle中的partition by語句，可以比較方便的實現(xiàn)按照字段分組，并且在分組內(nèi)按照指定字段排序的需求，如下是一個例子：

SELECT CourseName,
       StudentName,
       Grade,
       rank() over(PARTITION BY CourseName
                   ORDER BY Grade ASC) CourseRank
FROM grade

查詢結(jié)果如下：

這里的rank函數(shù)可以換成其他的函數(shù)，實現(xiàn)不同的排序需求。比如控制兩個相同分數(shù)的排名是否相同、以及如何處理并列排名之后的名次問題等。

4、說明

這個問題，oracle中應該有專門的partition by語法專門解決類似問題，MySql中應該也有GROUP_CONCAT聚合函數(shù)能夠配合SUBSTRING_INDEX截取函數(shù)可以實現(xiàn)類似功能。但是，這些高級的語法有一個局限性，他們不通用。了解這種自連接的思路可以幫助我們更好地理解SQL的強大語法特性。
當然，在工作中，可以根據(jù)具體的環(huán)境，盡量采用專門的語句來實現(xiàn)，這樣可讀性會好些。