给出了包含购买数据的下表.
CREATE TABLE myTable (
id INT NOT NULL AUTO_INCREMENT,
date DATETIME NOT NULL,
subNo SMALLINT NOT NULL,
poNo INT NOT NULL,
PRIMARY KEY (id))
INSERT INTO myTable VALUES (0, '2022-11-01 12:43', 1, 800), (0, '2022-11-02 13:00', 1, 800), (0, '2022-11-03 12:43', 2, 800), (0, '2022-11-03 14:00', 1, 923), (0, '2022-11-03 15:00', 2, 800), (0, '2022-11-04 12:43', 1, 800)
Id | Date | SubNo | PO# | ----|------------------|-------|-----| 100 | 2022-11-01 12:43 | 1 | 800 | 101 | 2022-11-02 13:00 | 1 | 800 | 102 | 2022-11-03 12:43 | 2 | 800 | 103 | 2022-11-03 14:00 | 1 | 923 | 104 | 2022-11-03 15:00 | 2 | 800 | 105 | 2022-11-04 12:43 | 1 | 800 |
SubNo是采购的子集或部分数量(PO编号)的序号.一次购买可以有30多个子集.
我正在寻找为给定采购的每个子集提供最新日期的查询.
对于PO 800,它将如下所示:
Id | Date | SubNo | PO# | ----|------------------|-------|-----| 105 | 2022-11-04 12:43 | 1 | 800 | 104 | 2022-11-03 15:00 | 2 | 800 |
我还没有找到过滤最新日期的方法. 粗略的做法是
SELECT id, date, subNo
FROM myTable
WHERE poNo=800
GROUP BY subNo
ORDER BY subNo, date DESC
但DISTINCT和GROUP BY不保证返回最新日期.
然后,我try 先创建一个视图,以便在以后的查询中使用.
CREATE VIEW myView AS
SELECT subNo s, (SELECT MAX(date) FROM myTable WHERE poNo=800 AND subNo=s) AS dd
FROM myTable
WHERE poNo=800
GROUP BY s
但是,尽管查询是OK的,但当用于视图时,结果不同,这可能是由于视图限制.
最后,我try 了一个联接表
SELECT id, datum, subNo s
FROM myTable my JOIN (SELECT MAX(date) AS d FROM myTable WHERE poNo=800 AND subNo=s) tmp ON my.date=tmp.d
WHERE poNo=800
但在WHERE子句中收到错误"UNKNOWN COLUMN"%s".
我的MySQL版本是8.0.22