MySQL 5.7 按最新记录分组

Question

非常简单的选择，但我很困惑:

create table users (id int, type int);
insert into users values(1, 100), (2, 101), (3, 100);
mysql> select * from users;                                                                                                               
+------+------+                                                                                                                           
| id   | type |                                                                                                                                                                                                   
+------+------+                                                                                                                                                                                                   
|    1 |  100 |                                                                                                                                                                                                   
|    2 |  101 |                                                                                                                                                                                                   
|    3 |  100 |                                                                                                                                                                                                   
+------+------+

我要得到结果:

+------+------+
| id   | type |
+------+------+
|    3 |  100 |
|    2 |  101 |
+------+------+

我的查询是:

MySQL 5.7 版返回:

mysql> select * from (select * from users order by id desc) as t group by type;
+------+------+
| id   | type |
+------+------+
|    1 |  100 |
|    2 |  101 |
+------+------+

在 MySQL 5.5 中，它按预期工作。 sql_mode 为 NULL

感谢您的回复。扩展表格以获得更清晰的结果:

create table users (id int, type int, level int);
insert into users values(1, 100, 1000), (2, 101, 1001), (3, 100, 1002);

mysql> select max(id), type, level from users group by type;
+---------+------+-------+
| max(id) | type | level |
+---------+------+-------+
|       3 |  100 |  1000 | <- expected 1002
|       2 |  101 |  1001 |
+---------+------+-------+

这个有效:

mysql> SELECT t1.* FROM users t1 INNER JOIN (SELECT type, MAX(id) AS max_id FROM users GROUP BY type) t2 ON t1.type = t2.type AND t1.id = t2.max_id ORDER BY id DESC;
+------+------+-------+
| id   | type | level |
+------+------+-------+
|    3 |  100 |  1002 |
|    2 |  101 |  1001 |
+------+------+-------+

Answer 1

你应该使用聚合函数和分组依据

select max(id), type
from users 
group by type;
order by id desc

mysql5.5 中不基于 group by 的结果是偶然的..(此行为在 sql 中已弃用，并且在 5.7 中的默认 sql_mode 中不允许)