访问 SQL 如何在 SELECT 查询中进行增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6294382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access SQL how to make an increment in SELECT query
提问by fingerman
I Have an SQL query giving me X results, I want the query output to have a coulmn called count making the query somthing like this:
我有一个 SQL 查询给我 X 个结果，我希望查询输出有一个名为 count 的 coulmn 使查询像这样：
count id section
1 15 7
2 3 2
3 54 1
4 7 4
How can I make this happen?
我怎样才能做到这一点？
采纳答案by Tao
Alright, I guess this comes close enough to constitute an answer: the following link specifies two approaches: http://www.techrepublic.com/blog/microsoft-office/an-access-query-that-returns-every-nth-record/
好吧，我想这足以构成一个答案：以下链接指定了两种方法：http: //www.techrepublic.com/blog/microsoft-office/an-access-query-that-returns-every-nth-记录/
The first approach assumes that you have an ID value and uses DCount (similar to @mikeY's solution).
第一种方法假设您有一个 ID 值并使用 DCount（类似于@mikeY 的解决方案）。
The second approach assumes you're OK creating a VBA function that will run once for EACH record in the recordset, and will need to be manually reset (with some VBA) every time you want to run the count - because it uses a "static" value to run its counter.
第二种方法假设您可以创建一个 VBA 函数，该函数将为记录集中的每个记录运行一次，并且每次要运行计数时都需要手动重置（使用某些 VBA） - 因为它使用“静态" 运行其计数器的值。
As long as you have reasonable numbers (hundreds, not thousands) or records, the second approach looks like the easiest/most powerful to me.
只要您有合理的数字（数百个，而不是数千个）或记录，第二种方法对我来说看起来是最简单/最强大的。
回答by HansUp
So in your example, "count" is the derived sequence number? I don't see what pattern is used to determine the count must be 1 for id=15 and 2 for id=3.
所以在你的例子中，“count”是派生的序列号？我没有看到使用什么模式来确定 id=15 的计数必须为 1，id=3 的计数必须为 2。
count id section
1 15 7
2 3 2
3 54 1
4 7 4
If id contained unique values, and you order by id you could have this:
如果 id 包含唯一值，并且您按 id 订购，您可以拥有：
count id section
1 3 2
2 7 4
3 15 7
4 54 1
Looks to me like mikeY's DSum approach could work. Or you could use a different approach to a ranking query as Allen Browne described at this page
在我看来，mikeY 的 DSum 方法可行。或者您可以使用不同的方法来进行排名查询，如本页中描述的 Allen Browne
Edit: You could use DCount instead of DSum. I don't know how the speed would compare between the two, but DCount avoids creating a field in the table simply to store a 1 for each row.
编辑：您可以使用 DCount 而不是 DSum。我不知道两者之间的速度比较如何，但 DCount 避免在表中创建一个字段，只是为了为每一行存储一个 1。
DCount("*","YourTableName","id<=" & [id]) AS counter
Whether you go with DCount or DSum, the counter values can include duplicates if the id values are not unique. If id is a primary key, no worries.
无论您使用 DCount 还是 DSum，如果 id 值不唯一，计数器值都可能包含重复项。如果 id 是主键，不用担心。
回答by David-W-Fenton
I frankly don't understand what it is you want, but if all you want is a sequence number displayed on your form, you can use a control bound to the form's CurrentRecord property. A control with the ControlSource =CurrentRecordwill have an always-accurate "record number" that is in sequence, and that will update when the form's Recordsource changes (which may or may not be desirable).
坦率地说，我不明白您想要什么，但如果您想要的只是表单上显示的序列号，您可以使用绑定到表单的 CurrentRecord 属性的控件。带有 ControlSource 的控件=CurrentRecord将有一个始终准确的“记录编号”，它是按顺序排列的，并且会在表单的 Recordsource 更改时更新（这可能是可取的，也可能是不可取的）。
You can then use that number to navigate around the form, if you like.
然后，如果您愿意，您可以使用该编号在表单中导航。
But this may not be anything like what you're looking for -- I simply can't tell from the question you've posted and the "clarifications" in comments.
但这可能与您正在寻找的不同 - 我根本无法从您发布的问题和评论中的“澄清”中判断出来。
回答by mikeY
The only trick I have seen is if you have a sequential id field, you can create a new field in which the value for each record is 1. Then you do a running sum of that field.
我见过的唯一技巧是，如果您有一个连续的 id 字段，您可以创建一个新字段，其中每条记录的值为 1。然后对该字段进行运行总和。
Add to your query
添加到您的查询
DSum("[New field with 1 in it]","[Table Name]","[ID field]<=" & [ID Field]) as counterthing
DSum("[New field with 1 in it]","[Table Name]","[ID field]<=" & [ID Field]) 作为对立
That should produce a sequential count in Access which is what I think you want.
这应该会在 Access 中产生一个顺序计数，这就是我认为你想要的。
HTH.
哈。
(Stolen from Rob Mills here: http://www.access-programmers.co.uk/forums/showthread.php?p=160386)
（从 Rob Mills 那里偷来的：http://www.access-programmers.co.uk/forums/showthread.php?p=160386 ）
回答by Maxali
You can use ROW_NUMBER() which is in SQL Server 2008
您可以使用 SQL Server 2008 中的 ROW_NUMBER()
SELECT ROW_NUMBER() OVER (ORDER By ID DESC) RowNum,
ID,
Section
FROM myTable
Then RowNum displays sequence of row numbers.
然后 RowNum 显示行号序列。
回答by user6788933
This function can be called from each record if available from a module.
如果从模块中可用，则可以从每个记录调用此函数。
Example: incrementingCounterTimeFlaged(10,[anyField]) should provide your query rows an int incrementing from 0.
示例： incrementingCounterTimeFlaged(10,[anyField]) 应该为您的查询行提供一个从 0 开始递增的 int。
'provides incrementing int values 0 to n
'resets to 0 some seconds after first call
Function incrementingCounterTimeFlaged(resetAfterSeconds As Integer,anyfield as variant) As Integer
Static resetAt As Date
Static i As Integer
'if reset date < now() set the flag and return 0
If DateDiff("s", resetAt, Now()) > 0 Then
resetAt = DateAdd("s", resetAfterSeconds, Now())
i = 0
incrementingCounterTimeFlaged = i
'if reset date > now increments and returns
Else
i = i + 1
incrementingCounterTimeFlaged = i
End If
End Function
回答by Yusuf Efendy
autoincrement in SQL
SQL中的自增
SELECT (Select COUNT(*) FROM table A where A.id<=b.id),B.id,B.Section FROM table AS B ORDER BY B.ID Asc
