MySQL之——GROUP BY分组取字段最大值_group by 最大值_冰河的博客

相关文章推荐

胡子拉碴的斑马 · AutoHotKey ...· 2 月前 ·

飘逸的煎鸡蛋 · Microencapsulation of ...· 1 年前 ·

刚失恋的啤酒 · spring security oidc ...· 1 年前 ·

玉树临风的汤圆 · Ansible系列(五)：各种变量定义方式和 ...· 1 年前 ·

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/54657412

假设有一个业务场景，需要查询用户登录记录信息，其中表结构如下：

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `ip` varchar(16) NOT NULL,
  `login_time` datetime,
  PRIMARY KEY (`id`),
  KEY (`uid`)
);

再来点测试数据：

INSERT INTO tb SELECT null, 1001, '192.168.1.1', '2017-01-21 16:30:47';
INSERT INTO tb SELECT null, 1003, '192.168.1.153', '2017-01-21 19:30:51';
INSERT INTO tb SELECT null, 1001, '192.168.1.61', '2017-01-21 16:50:41';
INSERT INTO tb SELECT null, 1002, '192.168.1.31', '2017-01-21 18:30:21';
INSERT INTO tb SELECT null, 1002, '192.168.1.66', '2017-01-21 19:12:32';
INSERT INTO tb SELECT null, 1001, '192.168.1.81', '2017-01-21 19:53:09';
INSERT INTO tb SELECT null, 1001, '192.168.1.231', '2017-01-21 19:55:34';

表数据情况：

+----+------+---------------+---------------------+
| id | uid  | ip            | login_time          |
+----+------+---------------+---------------------+
| 1  | 1001 | 192.168.1.1   | 2017-01-21 16:30:47 |
| 2  | 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 3  | 1001 | 192.168.1.61  | 2017-01-21 16:50:41 |
| 4  | 1002 | 192.168.1.31  | 2017-01-21 18:30:21 |
| 5  | 1002 | 192.168.1.66  | 2017-01-21 19:12:32 |
| 6  | 1001 | 192.168.1.81  | 2017-01-21 19:53:09 |
| 7  | 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+----+------+---------------+---------------------+

如果只需要针对用户查出其最后登录的时间，可以简单写出：

SELECT uid, max(login_time)
FROM tb
GROUP BY uid;

+------+---------------------+
| uid  | max(login_time)       |
+------+---------------------+
| 1001 | 2017-01-21 19:55:34 |
| 1002 | 2017-01-21 19:12:32 |
| 1003 | 2017-01-21 19:30:51 |
+------+---------------------+

若还需要查询用户最后登录时的其他信息，就不能用这种sql写了：

-- 错误写法
SELECT uid, ip, max(login_time)
FROM tb
GROUP BY uid;
-- 错误写法

这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的
（如果sql_mode开启了only_full_group_by，则不会执行成功。）
可能ip字段会取uid分组前的第一个row的值，显然不是所需信息
写一个子查询：

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time in (
SELECT max(login_time)
FROM tb
GROUP BY uid);

写法2
再或者换一个写法：

SELECT a.uid, a.ip, a.login_time
FROM tb a
WHERE a.login_time = (
SELECT max(login_time)
FROM tb
WHERE a.uid = uid);

顺便测了一下
在5.6以前的版本中，写法②这条sql在大数据量的情况下，执行计划不理想，目测性能不佳。
在5.6及以后的版本中，写法②这条sql会快很多，执行计划也有了改变
5.5.50：

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| 1  | PRIMARY            | a     | ALL  | NULL             | NULL  | NULL      | NULL | 7    | Using where |
| 2  | DEPENDENT SUBQUERY | tb    | ALL  | uid           | NULL  | NULL      | NULL | 7    | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

5.6.30：

+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref       | rows  | Extra      |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
| 1  | PRIMARY            | a     | ALL  | NULL              | NULL | NULL      | NULL        | 7    | Using where |
| 2  | DEPENDENT SUBQUERY | tb    | ref  | uid           | uid  | 4       | test.a.uid | 1    | NULL           |
+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+

写法3
直接改成join性能会更加好：

SELECT a.uid, a.ip, a.login_time
FROM (SELECT uid, max(login_time) login_time
FROM tb
GROUP BY uid
) b JOIN tb a ON a.uid = b.uid AND a.login_time = b.login_time;

当然，结果都相同：

+------+---------------+---------------------+
| uid  | ip            | login_time          |
+------+---------------+---------------------+
| 1003 | 192.168.1.153 | 2017-01-21 19:30:51 |
| 1002 | 192.168.1.66  | 2017-01-21 19:12:32 |
| 1001 | 192.168.1.231 | 2017-01-21 19:55:34 |
+------+---------------+---------------------+

注：如果要分组取最小值直接改对应函数和符号就行了。 假设有一个业务场景，需要查询用户登录记录信息，其中表结构如下：CREATE TABLE `tb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `ip` varchar(16) NOT NULL, `login_time` datetime, PRIMARY KEY (`id`), KEY import pandas as pd df = pd.DataFrame({'Sp':['a','b','c','d','e','f'], 'Mt':['s1', 's1', 's2','s2','s2','s3'], 'Value':[1,2,3,4,5,6], 'Count':[3,2,5,10,10,6]})

需求：查找 update_month 在指定范围，并且按 site 分组，返回分组中 update_month 最大的即最接近指定的时间的记录。site_test 这张表 (update_month, site) 已经建立了唯一索引。上面的 sql 查询在 Mysql 5.6 版本中测试没有问题。但是在 MySQL 5.7 版本中却报下面的 only_full_ group _by 错误。这其实是因为 MySQL 认为 id, name 这2个列不依赖于(not functionally depende

写这篇博文是为了帮助遇到同样问题的人，希望对你有用~问题：求怎么查出同一课程id中时间最大的记录？（如下图所示）1、刚开始想通过 group by 分组课程id, 然后获取 最大值 ，如下：SELECT MAX(l.learn_time) FROM learn l where l.user_id = '14201109' GROUP BY l.course_id查出来结果如下：但是我想要查出来所有的字...

类如有一个帖子的回复表，posts（ id , tid , subject , message , dateline ） , id 为自动增长字段， tid为该回复的主题帖子的id（外键关联）, subject 为回复标题， message 为回复内容， dateline 为回复时间，用UNIX 时间戳表示，现在要求选出前十个来自不同主题的最新回复 id int auto_increment primary key, score int null, time varchar(255) null, count int null, classify int ... 网上很多方法都是这样实现的（先倒序，在分组， group by 默认取最上面的一条数据）结果发现实际中这样做拿到的数据还是有误的 select * from (select * from scores order by score desc) s group by s.student_code 正确解决方法 (加imit99999999) select * fr `id` int not null auto_increment, `name` varchar(20) not null default '', `score` int not null default 0, primary key(`id`) )engine=InnoDB CHARSET=UTF8; 2. 插入如下数

您可以使用 MySQL 的 GROUP BY语句和MAX()聚合函数来查询表中某个时间 最大值 ，然后按照某个字段分组。假设您要查询的表名为`table_name`，时间字段为`time_field`，分组字段为` group _field`，则可以使用以下查询语句： SELECT group _field, MAX(time_field) AS max_time FROM table_name GROUP BY group _field; 这将返回每个分组字段的最大时间值。如果您还需要包括其他字段，则可以将它们添加到SELECT语句中，并将它们添加到 GROUP BY语句中，如下所示： SELECT group _field, other_field, MAX(time_field) AS max_time FROM table_name GROUP BY group _field, other_field; 这将返回每个分组字段和其他字段的最大时间值。