MySQL分组查找最早(大)或最晚(小)记录

想必大家也遇到过查找每日最早或最晚(最大或最小)记录这样的需求，那相应的SQL应该怎么写呢？

下面以查找玩家每日最早或最晚登陆记录作为例子记录下。先弄下测试表和数据:

CREATE TABLE `test`.`player_login` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `uid` VARCHAR(45) NOT NULL,
  `time` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `player_login_uid` (`uid` ASC),
  INDEX `player_login_time` (`time` ASC));

insert into test.player_login(uid,`time`) values('1',unix_timestamp('2018-09-10 08:10:30'));
insert into test.player_login(uid,`time`) values('2',unix_timestamp('2018-09-10 08:11:10'));
insert into test.player_login(uid,`time`) values('3',unix_timestamp('2018-09-10 08:15:01'));
insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-10 08:20:05'));

insert into test.player_login(uid,`time`) values('1',unix_timestamp('2018-09-10 10:10:30'));
insert into test.player_login(uid,`time`) values('2',unix_timestamp('2018-09-10 10:20:15'));
insert into test.player_login(uid,`time`) values('3',unix_timestamp('2018-09-10 11:05:30'));
insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-10 10:30:45'));

insert into test.player_login(uid,`time`) values('2',unix_timestamp('2018-09-11 07:20:15'));
insert into test.player_login(uid,`time`) values('3',unix_timestamp('2018-09-11 09:05:30'));
insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-11 10:30:45'));

insert into test.player_login(uid,`time`) values('3',unix_timestamp('2018-09-11 20:05:30'));
insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-11 21:30:45'));

insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-12 20:30:45'));
insert into test.player_login(uid,`time`) values('4',unix_timestamp('2018-09-12 23:30:45'));

对于查找最早记录最初写了个这样的SQL:

select uid, `time`, date_format(from_unixtime(`time`), '%Y-%m-%d') d, from_unixtime(`time`) loginTime from test.player_login group by uid, d order by d desc,  uid desc;

还凑效，也许是因为记录都是按时间顺序插入的，符合MySQL group by时对分组内的记录按某种顺序排序。但如果查找每日最晚登陆呢？傻逼了。于是想到了子查询:

select * 
from (
	select uid, `time`, date_format(from_unixtime(`time`), '%Y-%m-%d') d, from_unixtime(`time`) loginTime
    from test.player_login 
    order by `time` desc
) a
group by uid, a.d order by d desc, uid desc;

满心欢喜，走一个。WTF，和查询最早的结果一样，逗我呢！按道理讲，应该能得到正确的结果的啊！通过查找，知道真相的我眼泪掉下来。如果你是在MySQL5.7之前版本中测试，恭喜你得到了想要的结果，如果是之后(包含)的版本，同样恭喜你，得到了和我一样的结果。病危通知书：5.7版本以后对子查询排序做了优化，子查询全表排序失效。

不皮了，直接说结果吧：

select a.uid, a.`time`, date_format(from_unixtime(a.`time`), '%Y-%m-%d')  as d, from_unixtime(a.`time`) as loginTime 
from test.player_login a 
join (
	select uid, max(`time`) `latestTime` 
    from test.player_login 
    group by uid, date_format(from_unixtime(`time`), '%Y-%m-%d')) b 
on a.uid=b.uid and a.`time`=b.`latestTime` 
order by a.`time` desc, a.uid desc;

select a.uid, a.`time`, date_format(from_unixtime(a.`time`), '%Y-%m-%d')  as d, from_unixtime(a.`time`) as loginTime 
from test.player_login a 
join (
	select uid, min(`time`) `earlestTime` 
    from test.player_login 
    group by uid, date_format(from_unixtime(`time`), '%Y-%m-%d')) b 
on a.uid=b.uid and a.`time`=b.`earlestTime` 
order by a.`time` desc, a.uid desc;

可以看出是使用了连接查询，连接的部分查出了分组内的最值。