mysql - 如何使用分组名称获取 SQL 搜索的最后记录

Question

我有这张桌子：

+----+--------+-------------------+------------+
| id | name   | mac               | disk_usage |
+----+--------+-------------------+------------+
|  1 | simon  | 00::00:00:00:01   | 10         |
|  2 | monica | 00::00:00:00:02   | 10         |
|  3 | andrew | 00::00:00:00:03   | 10         |
|  4 | amon   | 00::00:00:00:04   | 10         |
|  5 | peter  | 00::00:00:00:05   | 10         |
|  6 | rusty  | 00::00:00:00:06   | 10         |
|  7 | simon  | 00::00:00:00:01   | 22         |
|  8 | amon   | 00::00:00:00:04   | 15         |
|  9 | monica | 00::00:00:00:02   | 18         |
| 10 | simon  | 00::00:00:00:01   | 2          |
+----+--------+-------------------+------------+

当我输入搜索词“mon”时，我想要这个结果：

+----+--------+-------------------+------------+
| id | name   | mac               | disk_usage |
+----+--------+-------------------+------------+
|  8 | amon   | 00::00:00:00:04   | 15         |
|  9 | monica | 00::00:00:00:02   | 18         |
| 10 | simon  | 00::00:00:00:01   |  2         |
+----+--------+-------------------+------------+

我正在使用这个 SQL 命令：

SELECT * FROM (SELECT test.* FROM test WHERE name LIKE "%mon%" ORDER BY id DESC)test GROUP BY name ORDER BY id DESC;

但我明白了：

+----+--------+-------------------+------------+
| id | name   | mac               | disk_usage |
+----+--------+-------------------+------------+
|  4 | amon   | 00::00:00:00:04   | 10         |
|  2 | monica | 00::00:00:00:02   | 10         |
|  1 | simon  | 00::00:00:00:01   | 10         |
+----+--------+-------------------+------------+

有人可以帮我解决这个问题吗？

标签： mysql

Answer 1

通过 id使用聚合函数max()，然后基于ID.

select * from test t
inner join 
    (select max(id) id, name
    from test 
    where name like '%mon%'
    group by name) t1 on t1.id = t.id
order by t.id asc