访问SQL查询以显示数据的更改历史记录

Question

(问题与this one类似，不同之处在于我的问题是1-in Access，而不是SQL Server，2-只检查一列的更改。)

以下是产品(SKU)的样本数据、购买日期和购买时的价格：

+----------------+------------+------------+
|      SKU       | Unit_Price |  Date_Due  |
+----------------+------------+------------+
| BHM1200NBACSTA | $13.00     | 11/2/2012  |
| BHM1200NBACSTA | $13.00     | 10/19/2012 |
| BHM1200NBACSTA | $13.00     | 9/28/2012  |
| BHM1200NBACSTA | $10.50     | 9/21/2012  |
| BHM1200NBACSTA | $13.00     | 8/27/2012  |
| BHM1200NBACSTA | $13.00     | 7/4/2012   |
| BHM1200NBACSTA | $12.50     | 6/19/2012  |
| BHM1700NDACSTA | $14.50     | 10/27/2012 |
| BHM1700NDACSTA | $14.73     | 9/29/2012  |
| BHM1700NDACSTA | $14.73     | 8/23/2012  |
| BHM1700NDACSTA | $14.50     | 6/20/2012  |
+----------------+------------+------------+

我只想看到任何给定日期范围的一个价格，所以给定的产品以相同的价格多次购买，我只想看到该产品在该价格下的第一个日期：

示例输出如下所示：

+----------------+------------+------------+
|      SKU       | Unit_Price |  Date_Due  |
+----------------+------------+------------+
| BHM1200NBACSTA | $13.00     | 9/28/2012  |
| BHM1200NBACSTA | $10.50     | 9/21/2012  |
| BHM1200NBACSTA | $13.00     | 7/4/2012   |
| BHM1200NBACSTA | $12.50     | 6/19/2012  |
| BHM1700NDACSTA | $14.50     | 10/27/2012 |
| BHM1700NDACSTA | $14.73     | 8/23/2012  |
| BHM1700NDACSTA | $14.50     | 6/20/2012  |
+----------------+------------+------------+

(请注意，价格可能会上升、下降，然后上升。我不仅想为每个产品提供一组唯一的价格，而且还希望为每个连续的日期范围提供一组唯一的价格。)

到目前为止，我已经使用了：

SELECT hbp1.SKU, hbp1.Date_Due, hbp1.Unit_Price
FROM Historical_Buy_Prices AS hbp1 
Left JOIN Historical_Buy_Prices AS hpb2 
    ON (hbp1.SKU = hpb2.SKU) 
    AND (hbp1.Date_Due + 1 = hpb2.Date_Due)
WHERE (hpb2.Date_Due IS NULL
    OR hbp1.Unit_Price <> hbp2.Unit_Price)
ORDER BY hbp1.SKU, hbp1.Date_Due;

当我运行它时，在显示结果之前，Access会提示我输入hbp2.Unit_Price的值，然后仍然返回重复的值。

我敢肯定这是我错过的一些简单的东西。你能帮个忙吗？谢谢,

已更新:要使用ddl创建表，请使用：

 CREATE TABLE Historical_Buy_Prices (SKU Text, UNIT_PRICE Currency, DATE_DUE Date
);

要插入示例数据，请使用：

INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 13.00, #11/2/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 13.00, #10/19/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 13.00, #9/28/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 10.50, #9/21/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 13.00, #8/27/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 13.00, #7/4/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1200NBACSTA", 12.50, #6/19/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1700NDACSTA", 14.50, #10/27/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1700NDACSTA", 14.73, #9/29/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1700NDACSTA", 14.73, #8/23/2012#);
INSERT INTO Historical_Buy_Prices VALUES ("BHM1700NDACSTA", 14.50, #6/20/2012#);

Answer 1

下面的查询使用子查询(别名为"sub")，该子查询又使用相关子查询来确定每个SKU和DATE_DUE对的先前价格。最外层的查询会过滤掉您不感兴趣的行。

您可以将"sub“子查询分解为单独的查询，以帮助理解它是如何工作的。

SELECT
    sub.SKU,
    sub.UNIT_PRICE,
    sub.DATE_DUE
FROM
    (
        SELECT
            h.SKU,
            h.DATE_DUE,
            h.UNIT_PRICE,
            (
                SELECT TOP 1 UNIT_PRICE
                FROM Historical_Buy_Prices
                WHERE
                        SKU = h.SKU
                    AND DATE_DUE < h.DATE_DUE
                ORDER BY DATE_DUE DESC
            ) AS previous_price
        FROM Historical_Buy_Prices AS h
    ) AS sub
WHERE
       sub.UNIT_PRICE <> [previous_price]
    OR sub.previous_price Is Null
ORDER BY
    sub.SKU,
    sub.DATE_DUE DESC;