单表每组数据最新一条记录
目标结果
分别通过相关子查询和独立子查询得到目标结果
相关子查询在某些MySQL和ADB版本中不支持, 因此需要采用独立子查询
分区
该SQL语句在8.0.25版本中执行成功, 在5.7.37版本中语法不支持
分组
分组
与group by + GROUP_CONCAT函数 方式一样, 只是将GROUP_CONCAT换成MAX, 但前提是, SQL的功能语义符合使用MAX函数
SELECT型相关子查询 与 LEFT JOIN 比较
主表
CREATE TABLE `t_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(30) DEFAULT NULL,
`item_no` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
从表
CREATE TABLE `item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_no` varchar(30) DEFAULT NULL,
`item_name` varchar(30) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
主表数据
----+-----------------+---------+---------------------+
| id | order_no | item_no | create_time |
+----+-----------------+---------+---------------------+
| 1 | D-20191210-0002 | A000 | 2023-08-06 04:02:06 |
| 2 | D-20230430-0091 | B000 | 2023-08-06 04:02:37 |
| 3 | D-20220729-0128 | C000 | 2023-08-06 04:02:59 |
| 4 | D-19021005-0030 | F000 | 2023-08-06 04:03:58 |
+----+-----------------+---------+---------------------+
从表数据
+----+---------+-----------+---------------------+
| id | item_no | item_name | create_time |
+----+---------+-----------+---------------------+
| 1 | A000 | A001 | 2023-08-06 04:06:51 |
| 2 | A000 | A002 | 2023-08-06 04:07:02 |
| 5 | A000 | A003 | 2023-08-06 04:07:27 |
| 3 | B000 | B001 | 2023-08-06 04:07:15 |
| 4 | B000 | B002 | 2023-08-06 04:07:22 |
| 6 | B000 | B003 | 2023-08-06 04:07:32 |
| 7 | C000 | C001 | 2023-08-06 04:07:42 |
| 8 | C000 | C002 | 2023-08-06 04:07:46 |
| 11 | C000 | C003 | 2023-08-06 04:08:15 |
| 9 | D000 | D001 | 2023-08-06 04:08:04 |
| 10 | D000 | D002 | 2023-08-06 04:08:08 |
| 12 | D000 | D003 | 2023-08-06 04:08:20 |
+----+---------+-----------+---------------------+
目标: 主从表关联查询, 在从表中查询每组数据(根据item_no分组)最新一条记录 目标结果
+----+-----------------+---------+---------------------+-----------+
| id | order_no | item_no | create_time | item_name |
+----+-----------------+---------+---------------------+-----------+
| 1 | D-20191210-0002 | A000 | 2023-08-06 04:02:06 | A003 |
| 2 | D-20230430-0091 | B000 | 2023-08-06 04:02:37 | B003 |
| 3 | D-20220729-0128 | C000 | 2023-08-06 04:02:59 | C003 |
| 4 | D-19021005-0030 | F000 | 2023-08-06 04:03:58 | NULL |
+----+-----------------+---------+---------------------+-----------+
一对多, 多中取一条分别通过相关子查询和独立子查询得到目标结果
SELECT
o.*,
( SELECT i.item_name FROM item i WHERE i.item_no = o.item_no ORDER BY i.create_time desc LIMIT 1 ) AS item_name
FROM
t_order o
执行计划
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | i | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 10.00 | Using where; Using filesort |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
SELECT
o.*,
i.item_name
FROM
t_order o
LEFT JOIN item i ON i.id = (
SELECT
t.id
FROM
item t
WHERE t.item_no = o.item_no
ORDER BY t.create_time desc
LIMIT 1)
执行计划
+----+--------------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | i | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 10.00 | Using where; Using filesort |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-----------------------------+
相关子查询在某些MySQL和ADB版本中不支持, 因此需要采用独立子查询
分区
该SQL语句在8.0.25版本中执行成功, 在5.7.37版本中语法不支持
SELECT
o.*,
t.item_name
FROM
t_order o
LEFT JOIN (
SELECT
i.item_no,
i.item_name,
row_number() over ( partition by i.item_no ORDER BY i.create_time desc ) row_num
FROM
item i
) t ON o.item_no = t.item_no AND t.row_num = 1;
执行计划
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------------+------+----------+----------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 101 | local.o.item_no,const | 2 | 100.00 | NULL |
| 2 | DERIVED | i | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------------+------+----------+----------------+
分组
SELECT
o.*,
t.item_name
FROM
t_order o
LEFT JOIN (
SELECT
i.item_no,
SUBSTRING_INDEX( GROUP_CONCAT( i.item_name ORDER BY i.create_time desc ), ',', 1 ) AS item_name
FROM
item i
GROUP BY i.item_no
) t ON t.item_no = o.item_no
执行计划
+----+-------------+------------+------------+------+---------------+-------------+---------+---------------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+---------------+------+----------+----------------+
| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 33 | db0.o.item_no | 2 | 100.00 | NULL |
| 2 | DERIVED | i | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+---------------+------+----------+----------------+
分组
SELECT
co.customer_order_id,
t.price
FROM
customer_order co
LEFT JOIN ( SELECT cod.customer_order_id, MAX( cod.price ) AS price FROM customer_order_detail cod GROUP BY cod.customer_order_id ) t ON t.customer_order_id = co.customer_order_id
与group by + GROUP_CONCAT函数 方式一样, 只是将GROUP_CONCAT换成MAX, 但前提是, SQL的功能语义符合使用MAX函数
WITH t AS (
SELECT
i1.*
FROM
item i1
LEFT JOIN item i2 ON i1.item_no = i2.item_no AND i1.create_time < i2.create_time
WHERE i2.create_time IS NULL
)
SELECT
o.*,
t.item_name
FROM
t_order o
LEFT JOIN t ON o.item_no = t.item_no
1.WITH语句中的内容, 即查询单表每组数据最新一条记录
2.以上SQL语法在5.7.37版本中不支持, 因此将WITH语句中的内容直接放在LEFT JOIN 之后位置.
SELECT
o.*,
t.item_name
FROM
t_order o
LEFT JOIN (
SELECT
i1.*
FROM
item i1
LEFT JOIN item i2 ON i1.item_no = i2.item_no AND i1.create_time < i2.create_time
WHERE i2.item_no IS NULL
) t ON o.item_no = t.item_no
执行计划
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | o | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | i1 | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | i2 | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
SELECT型相关子查询 与 LEFT JOIN 比较