msyql group by 分组获取最新一条数据

当在开发环境中，偶尔会遇到一种情况，mysql table 中针对用同个户信息保存了多条记录，而我们想要获取最近更新的记录

在常规中我们单纯的使用 group by ，然而并没有达到目的

eg:

select phone, FROM_UNIXTIME(created_at) as time FROM cms_h5_user WHERE phone=15812859010

+-------------+---------------------+
| phone       | time                |
+-------------+---------------------+
| 15812859010 | 2018-07-31 18:57:12 |
| 15812859010 | 2018-09-10 14:45:23 |
| 15812859010 | 2018-09-10 14:46:06 |
+-------------+---------------------+

想取出 time = 2018-09-10 14:46:06 最新的记录

select phone, FROM_UNIXTIME(created_at) as time FROM cms_h5_user WHERE phone=15812859010 GROUP BY phone ORDER BY time DESC;

+-------------+---------------------+
| phone       | time                |
+-------------+---------------------+
| 15812859010 | 2018-07-31 18:57:12 |
+-------------+---------------------+

很明显，不是我们想要的数据，即使对 time 进行倒序排序

分析：

msyql 写的顺序：select ... from... where.... group by... order by..

mysql 执行的顺序：from... where...group by.... select ... order by...

由此可以看出 order by 取到的数据是已经分组后的结果，所以这个时候针对同一用户 order by 等于是无效的

解决方法：

使用子查询

select phone, FROM_UNIXTIME(created_at) from (SELECT * FROM cms_h5_user ORDER BY created_at desc) cms_h5_user WHERE phone=15812859010 GROUP BY phone;

+-------------+---------------------------+
| phone       | FROM_UNIXTIME(created_at) |
+-------------+---------------------------+
| 15812859010 | 2018-09-10 14:46:06       |
+-------------+---------------------------+

这样就能完美的解决