mysql 分组 组内排序 实现

这是表数据展示

现在我用approval_item_id进行分组 每组按照时间倒序取最新一条

按照以前思路，先排序 order by，后进行group by

SELECT * FROM (SELECT * FROM approval_record ORDER BY approval_item_id,create_time DESC) a  GROUP BY approval_item_id;
+----------------------------------+---------------------+
| approval_item_id                 | create_time         |
+----------------------------------+---------------------+
| 54ec0536151e43f288fdbd706d83d5fb | 2020-07-23 11:20:50 |
| e254ab8f51d9493c969c642dd16fb810 | 2020-07-23 10:47:09 |
| 8194732897eb4d0e87120d3292049898 | 2020-07-22 17:11:50 |
+----------------------------------+---------------------+
3 rows in set (0.04 sec)

结果不对，明显order by 未生效

于是在此基础上，我用在子查询加上 DISTINCT

SELECT * FROM (SELECT DISTINCT * FROM approval_record ORDER BY approval_item_id,create_time DESC) a  GROUP BY approval_item_id;
+----------------------------------+---------------------+
| approval_item_id                 | create_time         |
+----------------------------------+---------------------+
| 8194732897eb4d0e87120d3292049898 | 2020-07-23 14:05:03 |
| 54ec0536151e43f288fdbd706d83d5fb | 2020-07-23 11:20:50 |
| e254ab8f51d9493c969c642dd16fb810 | 2020-07-23 10:47:09 |
+----------------------------------+---------------------+
3 rows in set (0.05 sec)

结果正确

执行过程解析如下

mariadb> EXPLAIN SELECT * FROM (SELECT * FROM approval_record ORDER BY approval_item_id,create_time DESC) a  GROUP BY approval_item_id;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | approval_record | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using temporary; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.06 sec)

mariadb> EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM approval_record ORDER BY approval_item_id,create_time DESC) a  GROUP BY approval_item_id;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using temporary; Using filesort |
|  2 | DERIVED     | approval_record | ALL  | NULL          | NULL | NULL    | NULL | 8    | Using filesort                  |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.07 sec)

有没有什么更直观的方法

SELECT approval_item_id,create_time FROM `approval_record`,
(SELECT GROUP_CONCAT(approval_record_id ORDER BY create_time DESC LIMIT 0,1) approval_record_id FROM approval_record GROUP BY approval_item_id)a
WHERE approval_record.approval_record_id=a.approval_record_id ORDER BY create_time desc;
+----------------------------------+---------------------+
| approval_item_id                 | create_time         |
+----------------------------------+---------------------+
| 8194732897eb4d0e87120d3292049898 | 2020-07-23 14:05:03 |
| 54ec0536151e43f288fdbd706d83d5fb | 2020-07-23 11:20:50 |
| e254ab8f51d9493c969c642dd16fb810 | 2020-07-23 10:47:09 |
+----------------------------------+---------------------+
3 rows in set (0.07 sec)

mariadb> EXPLAIN SELECT approval_item_id,create_time FROM `approval_record`,
(SELECT GROUP_CONCAT(approval_record_id ORDER BY create_time DESC LIMIT 0,1) approval_record_id FROM approval_record GROUP BY approval_item_id)a
WHERE approval_record.approval_record_id=a.approval_record_id ORDER BY create_time desc;
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------+------+----------------------------------------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref                  | rows | Extra                                        |
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL    | NULL          | NULL    | NULL    | NULL                 | 8    | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | approval_record | eq_ref | PRIMARY       | PRIMARY | 128     | a.approval_record_id | 1    | Using where                                  |
|  2 | DERIVED     | approval_record | ALL    | NULL          | NULL    | NULL    | NULL                 | 8    | Using filesort                               |
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------+------+----------------------------------------------+
3 rows in set (0.08 sec)