假设有一个业务场景，需要查询用户登录记录信息，其中表结构如下：
-
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', '2016-01-01 16:30:47';
-
INSERT INTO tb SELECT null, 1003, '192.168.1.153', '2016-01-01 19:30:51';
-
INSERT INTO tb SELECT null, 1001, '192.168.1.61', '2016-01-01 16:50:41';
-
INSERT INTO tb SELECT null, 1002, '192.168.1.31', '2016-01-01 18:30:21';
-
INSERT INTO tb SELECT null, 1002, '192.168.1.66', '2016-01-01 19:12:32';
-
INSERT INTO tb SELECT null, 1001, '192.168.1.81', '2016-01-01 19:53:09';
- INSERT INTO tb SELECT null, 1001, '192.168.1.231', '2016-01-01 19:55:34';
表数据情况：
-
+----+------+---------------+---------------------+
-
| id | uid | ip | login_time |
-
+----+------+---------------+---------------------+
-
| 1 | 1001 | 192.168.1.1 | 2016-01-01 16:30:47 |
-
| 2 | 1003 | 192.168.1.153 | 2016-01-01 19:30:51 |
-
| 3 | 1001 | 192.168.1.61 | 2016-01-01 16:50:41 |
-
| 4 | 1002 | 192.168.1.31 | 2016-01-01 18:30:21 |
-
| 5 | 1002 | 192.168.1.66 | 2016-01-01 19:12:32 |
-
| 6 | 1001 | 192.168.1.81 | 2016-01-01 19:53:09 |
-
| 7 | 1001 | 192.168.1.231 | 2016-01-01 19:55:34 |
- +----+------+---------------+---------------------+
如果只需要针对用户查出其最后登录的时间，可以简单写出：
-
SELECT uid, max(login_time)
-
FROM tb
-
GROUP BY uid;
-
-
+------+---------------------+
-
| uid | max(login_time) |
-
+------+---------------------+
-
| 1001 | 2016-01-01 19:55:34 |
-
| 1002 | 2016-01-01 19:12:32 |
-
| 1003 | 2016-01-01 19:30:51 |
- +------+---------------------+
若还需要查询用户最后登录时的其他信息，就不能用这种sql写了：
- -- 错误写法
-
SELECT uid, ip, max(login_time)
-
FROM tb
-
GROUP BY uid;
- -- 错误写法
（如果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);
写法②
再或者换一个写法：
-
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 |
- +----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
写法③
索性直接改成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 | 2016-01-01 19:30:51 |
-
| 1002 | 192.168.1.66 | 2016-01-01 19:12:32 |
-
| 1001 | 192.168.1.231 | 2016-01-01 19:55:34 |
- +------+---------------+---------------------+
当然……如果要分组取最小值直接改对应函数和符号就行了。
作者微信公众号（持续更新）
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29773961/viewspace-2125230/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- MySQL#复制 - 原生复制的一致性探讨
- gh-ost核心原理：数据一致性和cut-over
- mydumper使用及原理浅析
- MySQL主从复制延迟原因及处理思路
- 【Redis】slaveof 报错 Background transfer error
- 【MySQL】重放binlog故障一则 ERROR 1050 (42S01) : Table '' already exists
- 【MySQL】mysqldump备份失败与解决方案合集
- MySQL大小写敏感问题lower_case_table_names & lower_case_file_system
- 【MySQL】mysqldump Error 3024: Query execution was interrupted
- 【MySQL】部分5.6版本罕见复制报错 ERROR 1837
