滞后分区等效的MS Access [英] Lag Partition equivalent MS Access

问题描述

我正在寻找与MS Access中以下Oracle查询等效的产品.我知道延迟"(按分区)功能在MS Access中不可用，但正在寻求使用MS Access实现以下查询的相同功能.

I am looking for equivalent of following Oracle query in MS Access. I know that Lag Over (Partition by ) function is not available in MS Access but looking for to acheive same functionality of below query using MS Access.. Appreciate any inputs

select  t.*,
to_char(date1,'yyyymm') month,
lag(value1,1,0) over(partition by Id1,name1,office,product,overall order by date1)    prev_val
from  test_prev t
order by name1,
office,
overall,
date1,
product

推荐答案

从 Oracle进行工作示例，并考虑您的查询，您可能会发现适合的子查询.如果没有，则可能是用户定义的功能.

Working from the Oracle example and considering your query, you may find a subquery would suit. If not, a User Defined Function would be a possibility.

这将按product_id返回上一个订购日期

This returns the previous order date by product_id

SELECT Orders.ORDER_DATE, 
       Orders.PRODUCT_ID, 
       Orders.QTY, 
      (SELECT Top 1 o.Order_Date 
       FROM Orders o 
       WHERE o.Order_Date<Orders.Order_Date 
       AND o.Product_ID=Orders.Product_ID 
       ORDER BY o.Order_Date DESC) AS PreviousOrder
FROM Orders
ORDER BY Orders.ORDER_DATE, Orders.PRODUCT_ID

在示例数据中，每个product_id的每个订单具有不同的日期，因此仅具有ORDER BY日期的子查询就足够了，但是，Access中的热门查询返回匹配项，因此，如果可能重复，则最好进行订购通过相关字段和唯一ID.

In the sample data, each product_id has a different date for each order, so a subquery with ORDER BY date alone is sufficient, however, Top queries in Access return matches, so if duplicates are a possibility, it is best to order by the relevant field and a unique id.

这篇关于滞后分区等效的MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！