本文共 4045 字,大约阅读时间需要 13 分钟。
转载:https://segmentfault.com/a/1190000004157112
这里探讨了分组查询最大值(group-wise-max)的问题。涉及到 SQL 查询语句中的 GROUP BY 子句及连接(JOIN)操作。
本文缘起于 SegmentFault上 的一个问题:
下面是提问者的表和测试数据:
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);
可以理解成学生信息,简单的 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 |+----+-------+-----+-------+
问题:如何选出每班中年龄最大者?
使用 GROUP BY 子句,这一点毫无疑问。
select class, max(age) from test group by class;
+-------+----------+| class | max(age) |+-------+----------+| 1 | 42 || 2 | 20 || 3 | 33 |+-------+----------+
结果按 class
分组了,最大年龄也选出来了,但是没有 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 |+----+------+----------+-------+
结果并不正确,各列发生"错位",年龄 42 的应该是 liu 而不是 qiu,原因是它违反了下面这条规则:
包含 GROUP BY 的 SQL 语句,被 select 的列要么使用聚合函数,要么出现在GROUP BY 子句中。
上面的 SELECT 语句,id
,name
没有出现在 GROUP BY 子句,也没有使用聚合函数,所以它违反了规则,不是一条正确的SQL语句。
select t1.*from test t1, (select class, max(age) as age from test group by class) t2where 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 |+----+-------+-----+-------+
结果正确。
这条语句引用了两个表(t1
和 t2
),语义上相当于内连接(INNER JOIN)。
使用内连接改写上面那条语句。
注意:关键字 JOIN,就是指 INNER JOIN。当然你也可以显式地写成 INNER JOIN。
select t1.*from test t1join ( select class, max(age) as age from test group by class) t2on 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)来实现。没有用到 GROUP BY。
select t1.*from test t1left join test t2 on t1.class = t2.class and t1.age < t2.agewhere t2.class is null;
根据定义,左连接会从左表那里返回所有的行,即使在右表中没有匹配的行。
这条语句参考自:
原理在此:
摘录如下:
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.
可见,这条语句中的 WHERE 子句,其实可以用任何列作为条件。下面这句也是一样的效果:
select t1.*from test t1left join test t2 on t1.class = t2.class and t1.age < t2.agewhere t2.id is null;
把 zheng 的年龄改为 20,那么 class 2 中,qian 和 zheng 的年龄都是最大值 20。
update test set age=20 where name='zheng';
现在执行如上查询,结果为:
+----+-------+-----+-------+| id | name | age | class |+----+-------+-----+-------+| 3 | liu | 42 | 1 || 4 | qian | 20 | 2 || 5 | zheng | 20 | 2 || 6 | li | 33 | 3 |+----+-------+-----+-------+
使用内连接和左连接的两条语句,执行结果保持一致,都能显示出各组最大值的多行记录。
GROUP BY 子句将表按列的值分组,列的值相同的分在一组。如果 GROUP BY 后有多个列名,则先按第一列名分组,再按第二列名在组中分组,原则上可以一直分下去,直到在所有基本组中,GROUP BY 子句所指定的列都具有相同的值,HAVING 后的条件是选择基本组的条件。GROUP BY 子句常与聚集函数联用,此时聚集函数以基本组为计算对象。加了 GROUP BY 子句后,SELECT 子句所取的值必须在基本组中是唯一的,即只能是 GROUP BY 子句所指明的列或聚集函数。若无 GROUP BY 子句,则聚集函数以整个表为计算对象,此时 SELECT 子句只能取聚集函数,而不能取某一列。
王能斌,《数据库系统教程》(第二版),3.4.3。