摘要：現(xiàn)在執(zhí)行如上查詢，結(jié)果為使用內(nèi)連接和左連接的兩條語(yǔ)句，執(zhí)行結(jié)果保持一致，都能顯示出各組最大值的多行記錄。子句常與聚集函數(shù)聯(lián)用，此時(shí)聚集函數(shù)以基本組為計(jì)算對(duì)象。

這里探討了分組查詢最大值（group-wise-max）的問(wèn)題。涉及到 SQL 查詢語(yǔ)句中的 GROUP BY 子句及連接（JOIN）操作。

本文緣起于 SegmentFault上 的一個(gè)問(wèn)題：
http://segmentfault.com/q/1010000004138670

下面是提問(wèn)者的表和測(cè)試數(shù)據(jù)：

create table test (
    id smallint unsigned not null auto_increment,
    name varchar(20) not null,
    age smallint unsigned not null,
    class smallint unsigned not null,
    primary key (id));

insert into test (name, age, class) values
("wang", 11, 3), ("qiu", 22, 1), ("liu", 42, 1), ("qian", 20, 2),
("zheng", 20, 2), ("li", 33, 3);

可以理解成學(xué)生信息，簡(jiǎn)單的 SELECT 一下：

mysql> select * from test;
+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  1 | wang  |  11 |     3 |
|  2 | qiu   |  22 |     1 |
|  3 | liu   |  42 |     1 |
|  4 | qian  |  20 |     2 |
|  5 | zheng |  20 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

問(wèn)題：如何選出每班中年齡最大者？

使用 GROUP BY 子句，這一點(diǎn)毫無(wú)疑問(wèn)。

select class, max(age) from test group by class;

+-------+----------+
| class | max(age) |
+-------+----------+
|     1 |       42 |
|     2 |       20 |
|     3 |       33 |
+-------+----------+

結(jié)果按 class 分組了，最大年齡也選出來(lái)了，但是沒(méi)有 id 和 name。

添加其它列到 SELECT 子句。

select id, name, max(age), class from test group by class;

+----+------+----------+-------+
| id | name | max(age) | class |
+----+------+----------+-------+
|  2 | qiu  |       42 |     1 |
|  4 | qian |       20 |     2 |
|  1 | wang |       33 |     3 |
+----+------+----------+-------+

結(jié)果并不正確，各列發(fā)生"錯(cuò)位"，年齡 42 的應(yīng)該是 liu 而不是 qiu，原因是它違反了下面這條規(guī)則：

包含 GROUP BY 的 SQL 語(yǔ)句，被 select 的列要么使用聚合函數(shù)，要么出現(xiàn)在GROUP BY 子句中。

上面的 SELECT 語(yǔ)句，id，name 沒(méi)有出現(xiàn)在 GROUP BY 子句，也沒(méi)有使用聚合函數(shù)，所以它違反了規(guī)則，不是一條正確的SQL語(yǔ)句。

select t1.*
from test t1,
  (select class, max(age) as age from test group by class) t2
where t1.class = t2.class and t1.age = t2.age;

+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  5 | zheng |  22 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

結(jié)果正確。

這條語(yǔ)句引用了兩個(gè)表（t1 和 t2），語(yǔ)義上相當(dāng)于內(nèi)連接（INNER JOIN）。

使用內(nèi)連接改寫上面那條語(yǔ)句。

注意：關(guān)鍵字 JOIN，就是指 INNER JOIN。當(dāng)然你也可以顯式地寫成 INNER JOIN。

select t1.*
from test t1
join (
  select class, max(age) as age
  from test
  group by class) t2
on t1.class = t2.class and t1.age = t2.age;

+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  5 | zheng |  22 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

使用左連接（LEFT JOIN）來(lái)實(shí)現(xiàn)。沒(méi)有用到 GROUP BY。

select t1.*
from test t1
left join test t2 on t1.class = t2.class and t1.age < t2.age
where t2.class is null;

根據(jù)定義，左連接會(huì)從左表那里返回所有的行，即使在右表中沒(méi)有匹配的行。

這條語(yǔ)句參考自：The Rows Holding the Group-wise Maximum of a Certain Column

原理在此：JOIN Syntax

摘錄如下：

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.

可見(jiàn)，這條語(yǔ)句中的 WHERE 子句，其實(shí)可以用任何列作為條件。下面這句也是一樣的效果：

select t1.*
from test t1
left join test t2 on t1.class = t2.class and t1.age < t2.age
where t2.id is null;

把 zheng 的年齡改為 20，那么 class 2 中，qian 和 zheng 的年齡都是最大值 20。

update test set age=20 where name="zheng";

現(xiàn)在執(zhí)行如上查詢，結(jié)果為：

+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  4 | qian  |  20 |     2 |
|  5 | zheng |  20 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

使用內(nèi)連接和左連接的兩條語(yǔ)句，執(zhí)行結(jié)果保持一致，都能顯示出各組最大值的多行記錄。

GROUP BY 子句將表按列的值分組，列的值相同的分在一組。如果 GROUP BY 后有多個(gè)列名，則先按第一列名分組，再按第二列名在組中分組，原則上可以一直分下去，直到在所有基本組中，GROUP BY 子句所指定的列都具有相同的值，HAVING 后的條件是選擇基本組的條件。GROUP BY 子句常與聚集函數(shù)聯(lián)用，此時(shí)聚集函數(shù)以基本組為計(jì)算對(duì)象。加了 GROUP BY 子句后，SELECT 子句所取的值必須在基本組中是唯一的，即只能是 GROUP BY 子句所指明的列或聚集函數(shù)。若無(wú) GROUP BY 子句，則聚集函數(shù)以整個(gè)表為計(jì)算對(duì)象，此時(shí) SELECT 子句只能取聚集函數(shù)，而不能取某一列。
王能斌，《數(shù)據(jù)庫(kù)系統(tǒng)教程》（第二版），3.4.3。