转载自：/l1028386804/article/details/54657412
假设有一个业务场景，需要查询用户登录记录信息，其中表结构如下：
[sql]view plaincopy CREATETABLE`tb`(`id`int(11)NOTNULLAUTO_INCREMENT,`uid`int(11)NOTNULL,`ip`varchar(16)NOTNULL,`login_time`datetime,PRIMARYKEY(`id`),KEY(`uid`));
再来点测试数据：
[sql]view plaincopy INSERTINTOtbSELECTnull,1001,'192.168.1.1','-01-2116:30:47';INSERTINTOtbSELECTnull,1003,'192.168.1.153','-01-2119:30:51';INSERTINTOtbSELECTnull,1001,'192.168.1.61','-01-2116:50:41';INSERTINTOtbSELECTnull,1002,'192.168.1.31','-01-2118:30:21';INSERTINTOtbSELECTnull,1002,'192.168.1.66','-01-2119:12:32';INSERTINTOtbSELECTnull,1001,'192.168.1.81','-01-2119:53:09';INSERTINTOtbSELECTnull,1001,'192.168.1.231','-01-2119:55:34';
表数据情况：
[plain]view plaincopy +----+------+---------------+---------------------+|id|uid|ip|login_time|+----+------+---------------+---------------------+|1|1001|192.168.1.1|-01-2116:30:47||2|1003|192.168.1.153|-01-2119:30:51||3|1001|192.168.1.61|-01-2116:50:41||4|1002|192.168.1.31|-01-2118:30:21||5|1002|192.168.1.66|-01-2119:12:32||6|1001|192.168.1.81|-01-2119:53:09||7|1001|192.168.1.231|-01-2119:55:34|+----+------+---------------+---------------------+
如果只需要针对用户查出其最后登录的时间，可以简单写出：
[html]view plaincopy SELECTuid,max(login_time)FROMtbGROUPBYuid;[plain]view plaincopy +------+---------------------+|uid|max(login_time)|+------+---------------------+|1001|-01-2119:55:34||1002|-01-2119:12:32||1003|-01-2119:30:51|+------+---------------------+
若还需要查询用户最后登录时的其他信息，就不能用这种sql写了：
[sql]view plaincopy --错误写法SELECTuid,ip,max(login_time)FROMtbGROUPBYuid;--错误写法
这样的语句是非SQL标准的，虽然能够在MySQL数据库中执行成功，但返回的却是未知的
（如果sql_mode开启了only_full_group_by，则不会执行成功。）
可能ip字段会取uid分组前的第一个row的值，显然不是所需信息
写法1
写一个子查询：
[sql]view plaincopy SELECTa.uid,a.ip,a.login_timeFROMtbaWHEREa.login_timein(SELECTmax(login_time)FROMtbGROUPBYuid);
写法2
再或者换一个写法：
[sql]view plaincopy SELECTa.uid,a.ip,a.login_timeFROMtbaWHEREa.login_time=(SELECTmax(login_time)FROMtbWHEREa.uid=uid);
顺便测了一下
在5.6以前的版本中，写法②这条sql在大数据量的情况下，执行计划不理想，目测性能不佳。
在5.6及以后的版本中，写法②这条sql会快很多，执行计划也有了改变
5.5.50：
[plain]view plaincopy +----+--------------------+-------+------+---------------+------+---------+------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+|1|PRIMARY|a|ALL|NULL|NULL|NULL|NULL|7|Usingwhere||2|DEPENDENTSUBQUERY|tb|ALL|uid|NULL|NULL|NULL|7|Usingwhere|+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
5.6.30：
[plain]view plaincopy +----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+|1|PRIMARY|a|ALL|NULL|NULL|NULL|NULL|7|Usingwhere||2|DEPENDENTSUBQUERY|tb|ref|uid|uid|4|test.a.uid|1|NULL|+----+--------------------+-------+------+---------------+------+---------+------------+------+-------------+
写法3
直接改成join性能会更加好：
[sql]view plaincopy SELECTa.uid,a.ip,a.login_timeFROM(SELECTuid,max(login_time)login_timeFROMtbGROUPBYuid)bJOINtbaONa.uid=b.uidANDa.login_time=b.login_time;
当然，结果都相同：
[plain]view plaincopy +------+---------------+---------------------+|uid|ip|login_time|+------+---------------+---------------------+|1003|192.168.1.153|-01-2119:30:51||1002|192.168.1.66|-01-2119:12:32||1001|192.168.1.231|-01-2119:55:34|+------+---------------+---------------------+
