获取上个月的记录

Question

item    loc     year    month   quantity        startdate
XYZ     A       2020    1       3               23-06-2020
ABC     B       2020    2       218             24-06-2020
SDC     C       2020    6       107             25-06-2020
QWE     D       2020    7       144             25-06-2020
XYZ     A       2019    12      89              23-06-2020
ABC     B       2019    11      218             24-06-2020
SDC     C       2020    5       117             25-06-2020
QWE     D       2020    6       144             25-06-2020

如果我考虑上表，那么我的输出应如下所示：

item    loc     year    month   quantity        startdate
XYZ     A       2020    1       89              23-06-2020
ABC     B       2020    2       3               24-06-2020
SDC     C       2020    6       117             25-06-2020
QWE     D      2020    7        144             25-06-2020

因此，您可以看到仅数量值发生了变化，并且我们从前一个月和其余列中获取的值是原样的。

Answer 1

看起来你想要窗口函数lag().对于您的示例数据，这将产生所需的结果：

select *
from (
select 
item, 
loc,
year,
month,
lag(quantity) over(partition by item, loc order by year, month) quantity,
startdate
from mytable
) t
where quantity is not null

Answer 2

考虑在Access 数据库中工作的查询：

SELECT Table1.*, (SELECT TOP 1 quantity FROM Table1 AS Dupe 
WHERE Dupe.item = Table1.item AND Dupe.loc = Table1.loc 
AND DateSerial(Dupe.[Year],Dupe.[Month],1)<DateSerial(Table1.[Year],Table1.[Month],1) 
ORDER BY DateSerial(Dupe.[Year],Dupe.[Month],1)) AS PrevQty
FROM Table1;

如果要在月份序列中存在间隙时返回 0，请考虑：

SELECT Table1.*, Nz((SELECT quantity FROM Table1 AS Dupe
WHERE Dupe.item = Table1.item AND Dupe.loc = Table1.loc 
AND DateSerial(Dupe.[Year],Dupe.[Month],1)=DateAdd("m",-1,DateSerial(Table1.[Year],Table1.[Month],1)) 
ORDER BY DateSerial(Dupe.[Year],Dupe.[Month],1)),0) AS PrevQty
FROM Table1;

或

SELECT Q1.*, Nz(Q2.quantity,0) AS PrevQty FROM (
SELECT Table1.*, DateSerial([Year],[Month],1) AS FD FROM Table1) AS Q1 
LEFT JOIN (
SELECT Table1.*, DateAdd("m",+1,DateSerial([Year],[Month],1)) AS PD FROM Table1) AS Q2 
ON Q1.FD=Q2.PD AND Q1.item=Q2.item and Q1.loc=Q2.loc;