数据库分组查询最大值的问题

Render 发布于2019-08-06 14:56 / 288人阅读

摘要：现在执行如上查询，结果为使用内连接和左连接的两条语句，执行结果保持一致，都能显示出各组最大值的多行记录。子句常与聚集函数联用，此时聚集函数以基本组为计算对象。

这里探讨了分组查询最大值（group-wise-max）的问题。涉及到 SQL 查询语句中的 GROUP BY 子句及连接（JOIN）操作。

问题

本文缘起于 SegmentFault上的一个问题：
http://segmentfault.com/q/1010000004138670

下面是提问者的表和测试数据：

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) 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 |
+----+-------+-----+-------+

结果正确。

这条语句引用了两个表（t1 和 t2），语义上相当于内连接（INNER JOIN）。

第四次尝试

使用内连接改写上面那条语句。

注意：关键字 JOIN，就是指 INNER JOIN。当然你也可以显式地写成 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）来实现。没有用到 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;

根据定义，左连接会从左表那里返回所有的行，即使在右表中没有匹配的行。

这条语句参考自：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.

可见，这条语句中的 WHERE 子句，其实可以用任何列作为条件。下面这句也是一样的效果：

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";

现在执行如上查询，结果为：

+----+-------+-----+-------+
| 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 后有多个列名，则先按第一列名分组，再按第二列名在组中分组，原则上可以一直分下去，直到在所有基本组中，GROUP BY 子句所指定的列都具有相同的值，HAVING 后的条件是选择基本组的条件。GROUP BY 子句常与聚集函数联用，此时聚集函数以基本组为计算对象。加了 GROUP BY 子句后，SELECT 子句所取的值必须在基本组中是唯一的，即只能是 GROUP BY 子句所指明的列或聚集函数。若无 GROUP BY 子句，则聚集函数以整个表为计算对象，此时 SELECT 子句只能取聚集函数，而不能取某一列。
王能斌，《数据库系统教程》（第二版），3.4.3。

私有云超融合服务器 mongodb分组查询存储过程分组查询查询问题数据 php 以天分组查询

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/62927.html

数据库分组查询最大值的问题

摘要：现在执行如上查询，结果为使用内连接和左连接的两条语句，执行结果保持一致，都能显示出各组最大值的多行记录。子句常与聚集函数联用，此时聚集函数以基本组为计算对象。这里探讨了分组查询最大值（group-wise-max）的问题。涉及到 SQL 查询语句中的 GROUP BY 子句及连接（JOIN）操作。问题本文缘起于 SegmentFault上的一个问题：http://segment...

Rainie 2019-07-31 16:00 评论0 收藏0
数据库分组查询最大值的问题

摘要：现在执行如上查询，结果为使用内连接和左连接的两条语句，执行结果保持一致，都能显示出各组最大值的多行记录。子句常与聚集函数联用，此时聚集函数以基本组为计算对象。这里探讨了分组查询最大值（group-wise-max）的问题。涉及到 SQL 查询语句中的 GROUP BY 子句及连接（JOIN）操作。问题本文缘起于 SegmentFault上的一个问题：http://segment...

MkkHou 2019-06-25 18:16 评论0 收藏0
Oracle总结【SQL细节、多表查询、分组查询、分页】

摘要：前言在之前已经大概了解过数据库和学过相关的知识点，但是太久没用过了，就基本忘了印象中就只有基本的语句和相关一些概念写下本博文的原因就是记载着一些以前没注意到的知识点以后或许会有用实例与数据库概念数据库服务器由两部分组成实例理解为对象看不见的前言在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点，但是太久没用过Oracle了，就基本忘了...印象中就只有基本的SQL语...

陈伟 2019-07-23 16:50 评论0 收藏0
关系数据库SQL之基本数据查询:子查询、分组查询、模糊查询

摘要：连接查询涉及两个及以上的表查询为连接查询。查询二班学生成绩二班聚合函数查询聚合函数是一个值的集合为输入，返回单个值的函数。具体的数据库还会预定义一些其他常用的函数，比如字符串相聚合函数时间聚合函数。前言上一篇关系数据库常用SQL语句语法大全主要是关系型数据库大体结构，本文细说一下关系型数据库查询的SQL语法。 showImg(http://upload-images.jiansh...

kumfo 2019-08-06 13:57 评论0 收藏0
关系数据库SQL之基本数据查询:子查询、分组查询、模糊查询

摘要：连接查询涉及两个及以上的表查询为连接查询。查询二班学生成绩二班聚合函数查询聚合函数是一个值的集合为输入，返回单个值的函数。具体的数据库还会预定义一些其他常用的函数，比如字符串相聚合函数时间聚合函数。前言上一篇关系数据库常用SQL语句语法大全主要是关系型数据库大体结构，本文细说一下关系型数据库查询的SQL语法。 showImg(http://upload-images.jiansh...

VishKozus 2019-06-25 18:21 评论0 收藏0