【mysql】mysql group by 多表联查根据最新日期分组

场景是这样的:我需要通过关联多表查询出我想要的数据,然后我可能会根据id或者name之类的字段使用group by 进行分组,分组后我仅想把最新的一条记录显示出来,于是我需要根据数据库里对应数据的创建时间字段进行order by desc,但是如果你不进行嵌套查询的话,Mysql的语法仅支持group by在前,order by 在后,于是你会发现分类后的结果并不是你想要的,这样查出来的结果是创建时间最早的一条,而非最新创建的一条,于是你可能和我一样去网上寻找途径解决,但网上提到的大部分办法并不能生效,如果你需要的仅是某一列中的最大值,那你的确可以使用Max聚合函数来获取,但如果你是想要这条最大值对应的id以及其他对应数据,那使用max函数并不能满足你的需求(如下图),最后千辛万苦总算找到一条行之有效的解决方案,及时总结,备用,也为各位指条明路,避免被网上一些"不靠谱"的办法误导浪费时间.

 

如果觉得我上面描述的不够清楚,没关系,下面我举个实际的例子来讲解下:

这是我exam_eyesight表中的三条数据(可以忽略无关字段,仅看id,student_id,create_date即可)

我现在要查出这张表里student_id=1的三条数据,并通过group by 变为一条,而且是create_date最新的一条.

传统的sql我可能会写成下面这样:

 

 然后一看结果懵逼了,这不是把最旧的一条查出来了吗,我想要的是最新的啊...

想了下可能是因为先group by 然后才order by了,但把order by直接移到前面语法会报错:

于是修改代码成下面这样:

一运行,还TM是老样子,有毛用啊? 其实这个时候离成功已经很近了,正确的做法应该是在 order by 后面再加上limit xxx限制条件,这样即可达到想要的效果:

 

至于limt 后面的这串数字大小,其实可以根据业务量的大小去调整的,写1000也不会报错,具体看你库里有多少条数据了,写这么大是为了保险起见,那么是否可以无限大? 我也不清楚,反正我在后面添了几个0依旧是没有问题的,其实也没必要纠结这个数值的大小,尽量大一点即可,对大部分公司而言,数据量不会膨胀到几亿几十亿的,有的话也一般不会一次性把这些数据全查出来,数据库受不了.至于为什么后面要加这条limit语句才会生效,我也很好奇为啥会出现这种情况? 

我们用explain看一下Mysql的执行计划:

去掉limit xxxx之后,再次explain这句sql:

发现少了DERIVED,而且Extra里根本没有进行任何类型的排序,也就不奇怪为啥没生效了,那为啥一定要加这句Limit才会这样? 

我也不知道,这得问Mysql的开发人员了... 我猜可能是因为order by 和 group by联用对资源消耗比较大,不加Limit语句限制的话怕

系统吃不消,但这个值我试了下又可以设置的极度大,究竟意义何在?希望有知道的大神告知一下...

标题我提到了联表查,但这里仅是单表,其实原理是一样的,只是为了讲解简单易懂,联表的贴出来给大家参考下:

虽然没弄清楚为什么有点小遗憾,但问题确实完美解决了,所以特此开篇总结一下,以便今后遇到该类问题能够快速解决.