单表每组数据最新一条记录
目标结果
分别通过四种方式得到目标结果
分组
子查询中已得到每组符合条件的唯一数据
分组
子查询中已根据条件进行的归类分组,每组中的第一条数据即是符合条件的数据
NOT IN子查询改写成LEFT JOIN查询
以上执行计划仅作参考
参考外链
MySQL官方方案
CREATE TABLE `item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_no` varchar(30) DEFAULT NULL,
`item_name` varchar(30) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
+----+---------+-----------+---------+---------------------+
| id | item_no | item_name | version | create_time |
+----+---------+-----------+---------+---------------------+
| 1 | A000 | A001 | 0 | 2023-08-06 04:06:51 |
| 2 | A000 | A002 | 1 | 2023-08-06 04:07:02 |
| 3 | B000 | B001 | 0 | 2023-08-06 04:07:15 |
| 4 | B000 | B002 | 1 | 2023-08-06 04:07:22 |
| 5 | A000 | A003 | 3 | 2023-08-06 04:07:27 |
| 6 | B000 | B003 | 1 | 2023-08-06 04:07:32 |
| 7 | C000 | C001 | 0 | 2023-08-06 04:07:42 |
| 8 | C000 | C002 | 1 | 2023-08-06 04:07:46 |
| 9 | D000 | D001 | 1 | 2023-08-06 04:08:04 |
| 10 | D000 | D002 | 3 | 2023-08-06 04:08:08 |
| 11 | C000 | C003 | 2 | 2023-08-06 04:08:15 |
| 12 | D000 | D003 | 1 | 2023-08-06 04:08:20 |
| 13 | D000 | D004 | 3 | 2023-08-06 04:08:08 |
+----+---------+-----------+---------+---------------------+
目标: 查询表中每组数据(根据item_no分组)版本最高的记录,如果版本相同取时间最新. 目标结果
+----+---------+-----------+---------+---------------------+
| id | item_no | item_name | version | create_time |
+----+---------+-----------+---------+---------------------+
| 5 | A000 | A003 | 3 | 2023-08-06 04:07:27 |
| 6 | B000 | B003 | 1 | 2023-08-06 04:07:32 |
| 11 | C000 | C003 | 2 | 2023-08-06 04:08:15 |
| 10 | D000 | D002 | 3 | 2023-08-06 04:08:08 |
+----+---------+-----------+---------+---------------------+
SQL_MODE = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
version = 5.7.37
分别通过四种方式得到目标结果
SELECT i1.*
FROM item i1
WHERE i1.id = (
SELECT i2.id
FROM item i2
WHERE i1.item_no = i2.item_no ORDER BY i2.version desc, i2.create_time desc LIMIT 1)
执行计划
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: i1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: i2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13
filtered: 10.00
Extra: Using where; Using filesort
2 rows in set, 2 warnings (0.00 sec)
分组
子查询中已得到每组符合条件的唯一数据
SELECT i1.*
FROM item i1
INNER JOIN
(
SELECT SUBSTRING_INDEX( GROUP_CONCAT(id ORDER BY version desc,create_time desc),',',1) AS id
FROM item
GROUP BY item_no
) AS i2 ON i1.id = i2.id
执行计划
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: i1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: i2.id
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: item
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using filesort
3 rows in set, 1 warning (0.00 sec)
分组
子查询中已根据条件进行的归类分组,每组中的第一条数据即是符合条件的数据
SELECT *
FROM
(
SELECT *
FROM item
GROUP BY item_no
,version
,create_time
ORDER BY item_no
,version desc
,create_time desc
) AS t
GROUP BY item_no
或者
SELECT *
FROM
(
SELECT *
,row_number() over (partition by item_no ORDER BY version desc,create_time desc) AS row_num
FROM item
) AS t
GROUP BY item_no
执行计划
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: item
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 13
filtered: 100.00
Extra: Using temporary; Using filesort
2 rows in set, 1 warning (0.00 sec)
内层子查询对数据进行分组排序,结果如下
+----+---------+-----------+---------+---------------------+
| id | item_no | item_name | version | create_time |
+----+---------+-----------+---------+---------------------+
| 5 | A000 | A003 | 3 | 2023-08-06 04:07:27 |
| 2 | A000 | A002 | 1 | 2023-08-06 04:07:02 |
| 1 | A000 | A001 | 0 | 2023-08-06 04:06:51 |
| 6 | B000 | B003 | 1 | 2023-08-06 04:07:32 |
| 4 | B000 | B002 | 1 | 2023-08-06 04:07:22 |
| 3 | B000 | B001 | 0 | 2023-08-06 04:07:15 |
| 11 | C000 | C003 | 2 | 2023-08-06 04:08:15 |
| 8 | C000 | C002 | 1 | 2023-08-06 04:07:46 |
| 7 | C000 | C001 | 0 | 2023-08-06 04:07:42 |
| 10 | D000 | D002 | 3 | 2023-08-06 04:08:08 |
| 12 | D000 | D003 | 1 | 2023-08-06 04:08:20 |
| 9 | D000 | D001 | 1 | 2023-08-06 04:08:04 |
+----+---------+-----------+---------+---------------------+
外层查询再取每组第一条数据,得到结果
+----+---------+-----------+---------+---------------------+
| id | item_no | item_name | version | create_time |
+----+---------+-----------+---------+---------------------+
| 5 | A000 | A003 | 3 | 2023-08-06 04:07:27 |
| 6 | B000 | B003 | 1 | 2023-08-06 04:07:32 |
| 11 | C000 | C003 | 2 | 2023-08-06 04:08:15 |
| 10 | D000 | D002 | 3 | 2023-08-06 04:08:08 |
+----+---------+-----------+---------+---------------------+
Version:0.9 StartHTML:00000148 EndHTML:00001545 StartFragment:00000182 EndFragment:00001509 SourceURL:https://www.infuq.com/zone/Maximum.html
SELECT i1.*
FROM item i1
LEFT JOIN item i2 ON i1.item_no = i2.item_no AND (i1.version, i1.create_time) < (i2.version, i2.create_time)
WHERE i2.item_no IS NULL GROUP BY i1.item_no
或者
SELECT i1.*
FROM item i1
LEFT JOIN item i2 ON i1.item_no = i2.item_no AND (i1.version, i1.create_time, i1.id) < (i2.version, i2.create_time, i2.id)
WHERE i2.item_no IS NULL
执行计划
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: i2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
filtered: 10.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
NOT IN子查询改写成LEFT JOIN查询
以上执行计划仅作参考
参考外链
MySQL官方方案