英文:
How to get last two Sales' (by date) Product and Customer details from two separate tables in MySQL?
问题
我有两个表，一个是产品表
另一个是销售表
销售表中有日期，我想获取每个产品的最后两次购买记录，包括（产品名称，产品ID，数量，客户名称，客户ID），并按日期排序，但我不知道如何操作。
我尝试使用union，但无法限制到每个产品的最后两个订单（或已购买的产品）。
英文:
I have two tables 1 is Products Table
Another is the Sales Table
The sales table have dates in them, I want to get the last two purchase of each product with it's (ProductName, ProductID, Qty, CustName, CustID) ordered by date and I don't know how to go about doing that.
I tried using union but I can't limit it to last two orders of each product (or the products that has been purchased).
答案1
得分: 0
典型解决方案是使用 row_number()：
select P.ProductId, ProductName, CustId, CustName, Date
from Products P
left join (
select CustId, CustName, ProductId, Date,
row_number() over (partition by ProductId
order by Date desc, CustId desc) as rn
from Sales) LastSales
on P.ProductId = LastSales.ProductId and LastSales.rn <= 2
如果有多行在完全相同的时间卖出，上面的查询只返回其中两行。如果您想显示所有行，请使用 rank() 替代 row_number()：
rank() over (partition by ProductId order by Date desc)
英文:
Typical solution is row_number():
select P.ProductId, ProductName, CustId, CustName, Date
from Products P
left join (
select CustId, CustName, ProductId, Date,
row_number() over (partition by ProductId
order by Date desc, CustId desc) as rn
from Sales) LastSales
on P.ProductId = LastSales.ProductId and LastSales.rn <= 2
In case there are several rows sold exactly at the same time above query returns only two of them. If you want to show all use rank() instead of row_number():
rank() over (partition by ProductId order by Date desc)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论