mysql按照字段分组取最大值记录

最新推荐文章于 2023-06-06 16:37:50 发布

studymary

最新推荐文章于 2023-06-06 16:37:50 发布

阅读量673

点赞数

分类专栏： java 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/studymary/article/details/125888350

版权

java 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

mysql按照字段分组取最大值记录

数据库原始数据

mysql> select * from flow_information_event;
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| id | parent_id | school_id | event_type                   | school_flat_ids | school_flat_name | responsible_ids | responsible_name    | disponer_ids  | disponer_name       | del  | add_time            | last_update_time    |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| 10 | 10        |         2 | 再测一下哈哈哈               | 14              | 启德书院         | 13395           | 出奇迹              | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:10:32 | 2022-07-20 10:10:32 |
| 11 | 10        |         2 | 该测试了2234234324           | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:10:54 |
| 12 | 12        |         2 | 开始测试了呀啊啊             | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:11:40 | 2022-07-20 10:11:40 |
| 13 | 12        |         2 | 开始测试了呀啊啊2323         | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:13:06 |
| 14 | 14        |         2 | 自然灾害                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:42:36 | 2022-07-20 10:42:36 |
| 15 | 15        |         2 | 打架斗殴                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:42:49 | 2022-07-20 10:42:49 |
| 16 | 15        |         2 | 打架斗殴2                    | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:43:12 | 2022-07-20 10:43:12 |
| 17 | 15        |         2 | 打架斗殴3                    | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:43:20 | 2022-07-20 10:43:20 |
| 18 | 18        |         2 | 强闯闸机                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:44:04 | 2022-07-20 10:44:04 |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
9 rows in set (0.00 sec)

方式一：

mysql> select id, parent_id, school_id, event_type, school_flat_ids, school_flat_name, responsible_ids, responsible_name, disponer_ids, disponer_name, del, add_time, last_update_time from flow_information_event a where school_id = 2 and id = (select max(id) from flow_information_event where a.parent_id = parent_id) order by id desc limit 20;
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| id | parent_id | school_id | event_type                   | school_flat_ids | school_flat_name | responsible_ids | responsible_name    | disponer_ids  | disponer_name       | del  | add_time            | last_update_time    |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| 18 | 18        |         2 | 强闯闸机                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:44:04 | 2022-07-20 10:44:04 |
| 17 | 15        |         2 | 打架斗殴3                    | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:43:20 | 2022-07-20 10:43:20 |
| 14 | 14        |         2 | 自然灾害                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:42:36 | 2022-07-20 10:42:36 |
| 13 | 12        |         2 | 开始测试了呀啊啊2323         | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:13:06 |
| 11 | 10        |         2 | 该测试了2234234324           | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:10:54 |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

方式二：

mysql> select a.* from flow_information_event as a where not exists (select * from flow_information_event where parent_id=a.parent_id and id>a.id);
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| id | parent_id | school_id | event_type                   | school_flat_ids | school_flat_name | responsible_ids | responsible_name    | disponer_ids  | disponer_name       | del  | add_time            | last_update_time    |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
| 11 | 10        |         2 | 该测试了2234234324           | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:10:54 |
| 13 | 12        |         2 | 开始测试了呀啊啊2323         | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | NULL                | 2022-07-20 10:13:06 |
| 14 | 14        |         2 | 自然灾害                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:42:36 | 2022-07-20 10:42:36 |
| 17 | 15        |         2 | 打架斗殴3                    | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:43:20 | 2022-07-20 10:43:20 |
| 18 | 18        |         2 | 强闯闸机                     | 14              | 启德书院         | 2012475,13395   | 李小狼,出奇迹       | 2012475,13395 | 李小狼,出奇迹       |    0 | 2022-07-20 10:44:04 | 2022-07-20 10:44:04 |
+----+-----------+-----------+------------------------------+-----------------+------------------+-----------------+---------------------+---------------+---------------------+------+---------------------+---------------------+
5 rows in set (0.00 sec)