MySQL如何在搜索查询中获取链接表中的第二条最新记录

Question

我有一个MySQL DB，其中包含两个链接表产品和价格历史记录。它们通过产品 ID 字段链接。每次产品价格发生变化时，我都会创建一个新的历史记录。产品的最新历史记录具有最新价格。我还在产品表中存储当前价格。我想运行一个报告，我可以在其中检索第二个最后的价格历史记录，以便我可以比较当前和最后价格。我尝试了下面的sql查询，它返回了最新的价格历史记录，即当前价格。如何获得第二条最新价格历史记录？对于较新的记录，历史 ID 会更高，因为它是自动增量的，价格历史更新时间对于较新的记录也会更新，因此这可能是一种排序方式。 谢谢！

SELECT 
product.code, product.currentPrice, priceHistory.price, 
product.url, product.manuID, product.lastSeenTime,
priceHistory.updateTime, product.dateAdded, 
priceHistory.historyID 
FROM product, priceHistory 
WHERE product.idProduct = priceHistory.productID 
GROUP BY priceHistory.productID 
HAVING count(*) > 1 
ORDER BY `product`.`lastSeenTime` DESC

Answer 1

您可以使用ROW_NUMBER()窗口函数根据动态的任何顺序为行分配编号。完成此操作后，您可以简单地按该数字进行过滤。

例如：

with
h as (
select *,
row_number() over(partition by productid order by updatetime desc) as rn
from pricehistory
)
select
p.code,
p.currentprice,
h.price,
p.url,
p.manuid,
p.lastseentime,
h.updatetime,
p.dateadded,
h.historyid
from product p
left join h on h.productid = p.productid and h.rn = 2

编辑：

如果无法使用 CTE，则可以使用表表达式重写查询，如下所示：

select
p.code,
p.currentprice,
h.price,
p.url,
p.manuid,
p.lastseentime,
h.updatetime,
p.dateadded,
h.historyid
from product p
left join (
select *,
row_number() over(partition by productid order by updatetime desc) as rn
from pricehistory
) h on h.productid = p.productid and h.rn = 2