MS访问 - 对于给定变量的每次发生，增加“计数”值一个？ [英] MS Access - For each occurence of a given variable, increase a "count" value by one?
问题描述
我正在查找一个查询，它将：读取一列中的一行的当前值，将其与上面的行进行比较，并且如果上面的行是匹配，则顺序计数。基本上，这听起来像一个运行的计数，而不是一个Count（*），几乎像行应该去Count（Variable）-1在每一组行，直到它达到最小为零。以下示例假设我们有一个每日销售列表，并计算给定产品ID的出现次数。这是可能吗？
I'm looking for a query that will: read the current value of one row in one column, compare it to the row above it, and sequentially count up if the row above is a match. Essentially, this sounds like a running tally-up as opposed to a Count(*), almost like the rows should go Count(Variable)-1 on each set of rows until it hits a minimum of zero. The below example assumes we have a list of daily sales and counts the occurences of a given product ID. Is this possible?
选择计数（ProductID）AS SalesCount
未知公式作为SalesOccurence
Select Count(ProductID) AS SalesCount Unknown Formula as SalesOccurence
推荐答案
您描述的内容有时可以在Access SQL中使用包含其中一个字段之间的大于或小于关系的自连接来完成。例如，假设我们有一个名为[SalesHistory]的表，数据
What you describe can sometimes be accomplished in Access SQL using a self-join that includes a greater-than or less-than relation between one of the fields. For example, say we have a table named [SalesHistory] with the data
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为每个日期提供排名。
Notice that for t1.ProductID='001' there is one row returned for t1.SalesDate=#2013-10-16#, two rows for t1.SalesDate=#2013-10-17#, and so on. So, we can just tweak that query to count up the rows that are returned and that will give us the rank for each date by 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计数器：
If we save that query in Access as [SalesDateRanksByProduct] then we can use it in another query to display that DateRank "counter" alongside the original data:
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
这篇关于MS访问 - 对于给定变量的每次发生，增加“计数”值一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
