MySQL按组查询每组最新日期单条记录 分组取最新售价实操问题

需要从销售记录中取出父编码为ABC000001对应的所有子编码里，日期最新的售价记录，现有两张表的结构和测试数据如下：

CREATE TABLE `codes` (
  `code_father` longtext CHARACTER SET utf8mb4,
  `code_son` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `prices` (
  `code_son` varchar(22) CHARACTER SET utf8mb4 NOT NULL,
  `price` float,
  `date` date,
  KEY `code_son` (`code_son`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `codes`
(`code_father`,
`code_son`)
VALUES
('ABC000001','ADV000055'),
('ABC000001','ADV000045'),
('ABC000001','ADV000035'),
('ABC000001','ADV000015'),
('ABC000002','ADV000079'),
('ABC000002','ADV000077'),
('ABC000007','ADV000040'),
('ABC000008','ADV000030');

INSERT INTO `prices`
(`code_son`,
`price`,
`date`)
VALUES
('ADV000055','29.99','2021-11-06'),
('ADV000045','9.99','2021-12-04'),
('ADV000035','9.99','2021-12-01'),
('ADV000015','245.00','2021-12-06'),
('ADV000045','1999.99','2021-11-03'),
('ADV000035','29.99','2021-11-09'),
('ADV000079','29.99','2021-11-21'),
('ADV000077','29.99','2021-11-16'),
('ADV000077','29.99','2021-12-04'),
('ADV000040','29.99','2021-11-04'),
('ADV000030','29.99','2021-11-26'),
('ADV000030','29.99','2021-10-21');

原有查询语句无法得到正确结果：

SELECT c.code_father, c.code_son, p.price, p.date
FROM prices p
INNER JOIN (SELECT code_son, price, MAX(date)as date FROM prices GROUP BY code_son)as t1 USING(code_son, date)
LEFT JOIN codes c ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'

预期返回结果：

原有查询存在两个问题：

• 子查询中对code_son分组时直接选取了price，在MySQL非严格分组模式下，该字段取值为分组内随机记录的价格，并非对应最大日期的价格，关联后数据存在错误
• 原有逻辑仅取出了每个子编码各自的最新售价，没有进一步筛选出所有子编码中日期最大的记录，会返回多条结果不符合需求

基础写法（MySQL 5.5及以上兼容，无相同最大日期时适用） #

SELECT c.code_father, p.code_son, p.price, p.date
FROM codes c
INNER JOIN prices p ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'
ORDER BY p.date DESC
LIMIT 1;

逻辑说明：先过滤出目标父编码下的所有售价记录，按日期倒序排序后取第一条即可得到最新记录。

严谨写法（兼容同最大日期有多条记录的场景） #

SELECT c.code_father, p.code_son, p.price, p.date
FROM codes c
INNER JOIN prices p ON c.code_son = p.code_son
WHERE c.code_father = 'ABC000001'
AND p.date = (
    SELECT MAX(p2.date)
    FROM codes c2
    INNER JOIN prices p2 ON c2.code_son = p2.code_son
    WHERE c2.code_father = 'ABC000001'
)

逻辑说明：先查询出目标父编码下所有售价记录的最大日期，再匹配所有等于该日期的记录，避免同日期多记录时漏数。

内容的提问来源于stack exchange，提问作者Myers