在组MYSQL中只返回一条具有最近日期的记录

Question

因此，我有一个查询，我正在尝试获取几个记录中的最新销售价格。

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_father	code_son	日期
ABC000001	ADV000055	245.00	2021-12-06

首先，您必须将;更改为,，否则，将只插入第一条记录。

我想这可能就是你想要的。

我刚刚添加了一个订单，通过desc和limit。

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'
ORDER BY p.date DESC
LIMIT 1

什么是最近的销售价格？MAX价格还是MAX日期？

首先搜索MAX价格，如果有几个MAX价格，则搜索MAX日期

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