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

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 3 年前。

Improve this question

我有一个 MySQL 数据库，其中有两个链接表“产品”和“价格历史记录”。它们通过productID 字段链接。每当产品价格发生变化时，我都会创建新的历史记录。产品的最新历史记录具有最新价格。我还将当前价格存储在产品表中。我想运行一个报告，在其中可以检索第二个最新价格历史记录，以便我可以比较当前价格和最新价格。我尝试了下面的 sql 查询，它返回最新的价格历史记录，即当前价格。我怎样才能获得第二个最近的价格历史记录？对于较新的记录，historyID 会更高，因为它是自动增量，并且对于较新的记录，价格历史 updateTime 也会更新，因此这可能是一种排序方式。谢谢!

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