按键列值分组检索最新日期(MySql)

Question

给出如下表格中的采购数据。

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 |日期| SubNo | PO# |----|------------------|-------|-----|100 | 2022-11-01 12:43 | 1 | 800 |101 | 2022-11-02 13:00 | 1 | 800 |2012-11-03 12:43 | 2 | 800 |2022-11-03 14:00 | 1 | 923 |2012-11-03 15:00 | 2 | 800 |105 | 2022-11-04 12:43 | 1 | 800 |

SubNo是采购(PO#)的一个子集或部分数量的序数。一次购买可以有30多个子集。

我正在寻找为其每个子集的最新日期提供给定购买的查询。
对于PO 800，它看起来像这样:

Id |日期| SubNo | PO# |----|------------------|-------|-----|2012-11-04 12:43 | 1 | 800 |

我没有找到一种方法来过滤最近的日期。一个粗略的方法是

SELECT id, date, subNo
FROM myTable
WHERE poNo=800
GROUP BY subNo
ORDER BY subNo, date DESC

但是DISTINCT和GROUP BY不保证返回最近的日期。

然后我尝试先创建一个VIEW，以便在以后的查询中使用。

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

但是，虽然查询是正常的，但当用于VIEW时，结果不同，可能是由于VIEW的限制。

最后我尝试了一个join table

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子句中得到错误& '未知列's'。

MySql版本是8.0.22

Answer 1

您可以检查(date, subno)是否对应于(MAX(date)， subno)对中的一个:

SELECT id, date, subno
FROM mytable
WHERE pono = 800 AND (date, subno) IN (
SELECT MAX(date), subno
FROM mytable
WHERE pono = 800
GROUP BY subno
)
GROUP BY subno;

我的结果在一个干净的表:

+----+---------------------+-------+
| id | date                | subno |
+----+---------------------+-------+
|  6 | 2022-11-04 12:43:00 |     1 |
|  5 | 2022-11-03 15:00:00 |     2 |
+----+---------------------+-------+

根据您希望如何管理具有相同子号的多行最大值，您可能希望删除最后一个GROUP BY子号。用它，它只显示其中一个。如果没有，它将显示所有重复的最大行。

Answer 2

我们用row_number()，按SubNo和PO划分，按Date排序。

select Id 
,Date
,SubNo 
,PO
from
(
select  *
,row_number() over(partition by SubNo, PO order by Date desc) as rn
from    t
) t
where   rn = 1

<表类>Id日期SubNo阿宝tbody><<tr>1052022-11-04 12:43:0018001032022-11-03 14:00:0019231042022-11-03 15:00:002800