vba SQL Server 循环记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6628153/
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
SQL Server Loop through records
提问by Taryn
I have been online looking for the best way to rewrite this current VBA/MS Access Sub to work in a stored procedure in SQL server 2005. We are not supposed to use CURSORSfor items unless we can fully explain the reasons why we cannot do this any other way.
我一直在网上寻找重写此当前 VBA/MS Access Sub 以在 SQL Server 2005 中的存储过程中工作的最佳方法。CURSORS除非我们能完全解释我们不能这样做的原因，否则我们不应该使用它另一种方式。
Current Code:
当前代码：
Public Sub updcsh()
Dim tranamt
Dim Acct
Dim acct2
Dim Csh
Dim recSet As Recordset
DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
Set recSet = CurrentDb.OpenRecordset("FndVios")
recSet.MoveFirst
Do Until recSet.EOF
Acct = recSet![ID]
tranamt = recSet![TAM]
If Acct <> acct2 Then
Csh = recSet![Due]
End If
Csh = Csh+ tranamt
acct2 = Acct
recSet.Edit
recSet![Due] = Csh
recSet.Update
recSet.MoveNext
Loop
End Sub
The record set that is being opened is below:
正在打开的记录集如下：
SELECT ID, EXEC_TM, Due, TAM
FROM FRT
ORDER BY ID, EXEC_DT, EXEC_TM, D_C_CD, SEQ_NBR, TAM DESC
I searched through SO and found this. I looked at cursors and other methods on how to write this for a stored proc and I cannot figure out the correct way to rewrite this.
我搜索了 SO 并找到了这个。我查看了有关如何为存储过程编写此代码的游标和其他方法，但我无法找出重写此代码的正确方法。
Can anyone offer up any suggestions? Should I use a temp table? I have not done this before and I am stumped at how to proceed.
任何人都可以提供任何建议吗？我应该使用临时表吗？我以前没有这样做过，我对如何继续感到困惑。
EDIT:
编辑：
Sample Data
Original:
ID EXEC_TM Due TAM
12345678 12343811 50.81 $-6561.91
12345678 12343822 50.81 $-4374.63
12345678 15581917 50.81 $-4762.76
Final Result:
ID EXEC_TM Due TAM
12345678 12343811 88.87 $-6561.91
12345678 12343822 $-1685.76 $-4374.63
12345678 15581917 $-6448.52 $-4762.76
回答by JNK
Tom's answer is a better one from a process redesign perspective. The code below is a close recreation of your current logic in CURSORform.
从流程重新设计的角度来看，汤姆的答案是更好的答案。下面的代码是对您当前的逻辑逻辑的一种接近再现CURSOR。
I by no means endorse this methodbut this is what you asked for. You may need to correct datatypes etc. for it to work correctly.
我绝不赞同这种方法，但这正是您所要求的。您可能需要更正数据类型等才能正常工作。
DECLARE @ID int,
@Exec_TM int,
@Due money,
@TAM money,
@ID2 int = 0,
@Cash money
DECLARE RecSet CURSOR FOR
SELECT ID, EXEC_TM, Due, TAM
FROM FRT
ORDER BY ID, EXEC_DT, EXEC_TM, D_C_CD, SEQ_NBR, TAM DESC
OPEN RecSet
FETCH NEXT FROM RecSet INTO @ID, @Exec_TM, @Due, @TAM
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @ID <> @ID2 SET @Cash = @Due
SET @Cash = @Cash + @TAM
SET @ID2 = @ID
UPDATE FRT
SET Due = @Cash
WHERE ID = @ID
AND EXEC_TM = @Exec_TM
AND Due = @Due
AND TAM = @TAM
FETCH NEXT FROM RecSet INTO @ID, @Exec_TM, @Due, @TAM
END
CLOSE RecSet
DEALLOCATE RecSet
回答by Tom H
First, keeping the "Due" amount in there is a horrible idea from a database design standpoint (and probably from an accounting standpoint).
首先，从数据库设计的角度（并且可能从会计的角度），保留“到期”金额是一个可怕的想法。
Second, with your logic if there is a transaction amount on the first row for an account then every time you run this process it will throw off your due amount further and further until you start to owe the customers money.
其次，根据您的逻辑，如果帐户的第一行有交易金额，那么每次运行此过程时，它都会越来越多地抛弃您的到期金额，直到您开始欠客户钱。
Here is a basic set-based approach though for calculating running totals:
这是一种基于集合的基本方法，用于计算运行总数：
SELECT
L1.account_id,
L1.transaction_date,
L1.amt,
SUM(L2.amt)
FROM
Ledger L1
INNER JOIN Ledger L2 ON
L2.account_id = L1.account_id AND
L2.transaction_date <= L1.transaction_date
GROUP BY
L1.account_id,
L1.transaction_date,
L1.amt
Because you have that "due" column separate, it will cause problems here because it looks like you're only counting that for the first entry. You can account for that in the query above, but it will involve either a subquery or additional joins.
因为您将“到期”列分开，所以在这里会导致问题，因为看起来您只计算第一个条目的数量。您可以在上面的查询中说明这一点，但它会涉及子查询或附加连接。
