标签 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;

- 阅读剩余部分 -