MySQL分组查找最早(大)或最晚(小)记录 |
您所在的位置：网站首页 › 如何查找历史航班信息表呢 › 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;可以看出是使用了连接查询，连接的部分查出了分组内的最值。 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |