您所描述的有时可以使用 Access SQL 来完成自加入其中包括其中一个字段之间的大于或小于关系。例如,假设我们有一个名为 [SalesHistory] 的表,其中包含数据
ProductID SalesDate DailySales
--------- ---------- ----------
001 2013-10-16 225
001 2013-10-17 195
001 2013-10-18 250
002 2013-10-16 350
002 2013-10-17 375
002 2013-10-18 330
查询
SELECT t1.ProductID, t1.SalesDate, t2.ProductID, t2.SalesDate
FROM
SalesHistory t1
INNER JOIN
SalesHistory t2
ON t1.ProductID = t2.ProductID
AND t1.SalesDate >= t2.SalesDate
ORDER BY t1.ProductID, t1.SalesDate
返回以下结果
t1.ProductID t1.SalesDate t2.ProductID t2.SalesDate
------------ ------------ ------------ ------------
001 2013-10-16 001 2013-10-16
001 2013-10-17 001 2013-10-17
001 2013-10-17 001 2013-10-16
001 2013-10-18 001 2013-10-18
001 2013-10-18 001 2013-10-17
001 2013-10-18 001 2013-10-16
002 2013-10-16 002 2013-10-16
002 2013-10-17 002 2013-10-17
002 2013-10-17 002 2013-10-16
002 2013-10-18 002 2013-10-18
002 2013-10-18 002 2013-10-17
002 2013-10-18 002 2013-10-16
请注意,对于t1.ProductID='001'
返回一行t1.SalesDate=#2013-10-16#
, 两行t1.SalesDate=#2013-10-17#
, 等等。因此,我们可以调整该查询来计算返回的行数,这将为我们提供按 ProductID 排列的每个日期的排名
SELECT t1.ProductID, t1.SalesDate, COUNT(*) AS DateRank
FROM
SalesHistory t1
INNER JOIN
SalesHistory t2
ON t1.ProductID = t2.ProductID
AND t1.SalesDate >= t2.SalesDate
GROUP BY t1.ProductID, t1.SalesDate
...返回:
ProductID SalesDate DateRank
--------- ---------- --------
001 2013-10-16 1
001 2013-10-17 2
001 2013-10-18 3
002 2013-10-16 1
002 2013-10-17 2
002 2013-10-18 3
如果我们在 Access 中将该查询保存为 [SalesDateRanksByProduct],那么我们可以在另一个查询中使用它来在原始数据旁边显示该 DateRank“计数器”:
SELECT
SalesHistory.ProductID,
SalesHistory.SalesDate,
SalesHistory.DailySales,
SalesDateRanksByProduct.DateRank
FROM
SalesHistory
INNER JOIN
SalesDateRanksByProduct
ON (SalesHistory.SalesDate = SalesDateRanksByProduct.SalesDate)
AND (SalesHistory.ProductID = SalesDateRanksByProduct.ProductID)
ORDER BY SalesHistory.ProductID, SalesHistory.SalesDate;
...返回:
ProductID SalesDate DailySales DateRank
--------- ---------- ---------- --------
001 2013-10-16 225 1
001 2013-10-17 195 2
001 2013-10-18 250 3
002 2013-10-16 350 1
002 2013-10-17 375 2
002 2013-10-18 330 3