MySQL 5.7 按最新记录分组 [英] MySQL 5.7 group by latest record
本文介绍了MySQL 5.7 按最新记录分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
非常简单的选择，但我很困惑:
Very simple select, but I'm confused:
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 version 5.7 returns:
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 为空
At MySQL 5.5 it works as expected. sql_mode is NULL
感谢您的回复.扩展表格以获得更清晰的结果:
Thanks for the replies. Extended table to have more clear results:
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 |
+------+------+-------+
推荐答案
为了完整起见，我可以提供以下查询，如果您想返回具有任意列数的整个记录，该查询将起作用.
For completeness I can offer the following query which would work if you wanted to return entire records with an arbitrary number of columns.
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
这篇关于MySQL 5.7 按最新记录分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
