MySQL: 先ORDER 再 GROUP

有一个日志表，记录每日(date)不同类型(datatype) 数据值 格式如下

mysql> select * from userlog;

+-----+------------+----------+-----------+--------+
| uid | date       | datatype | datavalue | logger |
+-----+------------+----------+-----------+--------+
|   4 | 2020-03-03 |        1 |        83 |      2 |
|   4 | 2020-04-04 |        1 |        82 |      1 |
|   5 | 2020-03-03 |        1 |        83 |      2 |
|   6 | 2020-03-03 |        1 |        70 |      3 |
|   6 | 2020-04-04 |        5 |        23 |      3 |
|   6 | 2020-04-04 |        6 |        20 |      3 |
|   6 | 2020-04-04 |        7 |        37 |      3 |
|   6 | 2020-04-04 |       10 |        38 |      3 |
+-----+------------+----------+-----------+--------+
23 rows in set (0.00 sec)

我需要列出各项数据的最新状态时，并用了 group by 和 order by , 发现sql脚本并未如我期望地展示数据

mysql> SELECT * FROM userlog WHERE uid = 4 GROUP BY datatype ORDER BY date DESC;

+-----+------------+----------+-----------+--------+
| uid | date       | datatype | datavalue | logger |
+-----+------------+----------+-----------+--------+
|   4 | 2020-03-03 |        1 |        83 |      2 |
+-----+------------+----------+-----------+--------+
1 row in set (0.00 sec)

原因很好理解，处理的顺序是 先按自然顺序 group 了，才对结果 date 做 order DESC;

查了下往上的方法，用子查询的做法似乎5.7版本因mysql的语义解释优化后也无效。参考了网友提供的若干方案，最后选取了这种方式实现

SELECT A.* FROM userlog A JOIN (
SELECT max(date) as date,uid, datatype
FROM userlog
WHERE uid = 4
GROUP BY datatype
) TMP
ON A.uid = TMP.uid AND A.date = TMP.date AND A.datatype = TMP.datatype
WHERE A.uid = 4;

+-----+------------+----------+-----------+--------+
| uid | date       | datatype | datavalue | logger |
+-----+------------+----------+-----------+--------+
|   4 | 2020-04-04 |        1 |        82 |      1 |
+-----+------------+----------+-----------+--------+
1 row in set (0.00 sec)

原理是内联自身(TMP)，先把自己的数据按照类型和日期group了且取出date最大值，进行匹配。

参考文： https://segmentfault.com/q/1010000011383702