选择最近更改的项目

Question

我想选择最近根据时间戳更改的项目编号，THEN使用这些项目编号来选择/查找具有bin=MASTER的项目编号。下面是我尝试过的：

SELECT  (SELECT cItemno FROM TB_BinLoc WHERE Updates > {ts '2020-12-17 05:00:00'} ) as x , cBin
FROM TB_BinLoc y
WHERE y.cItemno = x AND cBin = 'MASTER'

===============这就是我最终得到的：

SELECT cItemno, cWarehouse, cBin, nCounted, Updates FROM TB_BinLoc WHERE Updates > {ts '2020-12-17 05:00:00'}
UNION
SELECT DISTINCT y.cItemno, y.cWarehouse, y.cBin, y.nCounted, y.Updates
FROM TB_BinLoc y
JOIN TB_BinLoc x on y.cItemno = x.cItemno
Where y.cBin = 'MASTER' AND y.cWarehouse = 'MAIN'
AND x.Updates > {ts '2020-12-17 05:00:00'}
ORDER BY cBin ASC

Answer 1

按如下方式使用join：

SELECT distinct x.cItemno as x , y.cBin
FROM TB_BinLoc y
Join TB_BinLoc x on y.cItemno = x.cItemno
Where y.cBin = 'MASTER'
And x.Updates > {ts '2020-12-17 05:00:00'}

Answer 2

这可能是使用窗口函数的好地方：

SELECT bl.*
FROM (SELECT bl.*, MAX(updates) OVER (PARTITION BY cItemNo) as max_update
FROM TB_BinLoc bl
) bl
WHERE bl.cItemno = x AND blcBin = 'MASTER' AND
bl.max_update > {ts '2020-12-17 05:00:00'}