如何实现动态查询余额
----------------------------------------------------------------
表：
----------------------------------------------------------------
id 自动增加 长整
in 货币
out 货币
----------------------------------------------------------------
代码：
----------------------------------------------------------------
Option Compare Database
Option Explicit
Public gcurLastBalance As Currency '上次计算的余额
Public glngLastID As Long '上次的 ID
'查询余额
'Version 1.0
'2003-05-06-15-15
'By Roadbeg
'要求以 Id 作为判断依据.(长整型)
Public Function GetBalance(ID As Long) As Currency
On Error GoTo Doerr
Dim curIn As Currency, curOut As Currency
Dim curRe As Currency
If glngLastID <> 0 Then
If ID > glngLastID Then
curIn = Nz(DSum("[IN]", "TEST", "ID <=" & str(ID) & " and ID>" & str(glngLastID)))
curOut = Nz(DSum("[OUT]", "TEST", "ID <=" & str(ID) & " and ID>" & str(glngLastID)))
curRe = gcurLastBalance + curIn - curOut
ElseIf ID < glngLastID Then
curIn = Nz(DSum("[IN]", "TEST", "ID >" & str(ID) & " and ID<=" & str(glngLastID)))
curOut = Nz(DSum("[OUT]", "TEST", "ID >" & str(ID) & " and ID<=" & str(glngLastID)))
curRe = gcurLastBalance - curIn + curOut
ElseIf ID = glngLastID Then
curRe = gcurLastBalance
End If
Else
curIn = DSum("[IN]", "TEST", "ID<=" & str(ID))
curOut = DSum("[OUT]", "TEST", "ID<=" & str(ID))
curRe = curIn - curOut
End If
' Debug.Print ID
glngLastID = ID
gcurLastBalance = curRe
GetBalance = curRe
Doerr:
End Function
'改变了 test 表的记录值后,请调用此函数以强制 GetBalance 函数刷新.
Public Sub ResetBalance()
gcurLastBalance = 0
glngLastID = 0
End Sub
'这是 lwwvb 版主的函数,我将它改为以 id 作为计算依据了,原理不变.
Public Function f(d As Long) As Currency
Dim a As Currency
Dim b As Currency
a = Nz(DSum("[in]", "test", "id <=" & str(d)))
b = Nz(DSum("[out]", "test", "id <=" & str(d)))
f = a - b
End Function
'请使用以下函数产生 600000 条随机记录,以检验函数在记录较多时的效果.
Public Sub 产生随机记录()
Dim rst As DAO.Recordset
Dim i As Long
Debug.Print Now()
Set rst = CurrentDb.OpenRecordset("select [in] as dataa,[out] as datab from test")
For i = 0 To 600000
rst.AddNew
rst!dataa = CLng(Rnd() * 100)
rst!datab = CLng(Rnd() * 100)
rst.Update
Next i
rst.Close
Debug.Print Now()
End Sub
'一下是一组时间测试
Function t2()
Dim c1 As New class1
Dim rs As ADODB.Recordset
c1.Reset
Set rs = CurrentProject.Connection.Execute("SELECT [id], [in], [out], getbalance([id]) AS 余额 FROM test ORDER BY [id];")
Debug.Print c1.Elapsed
Set rs = Nothing
Set c1 = Nothing
End Function
Function t3()
Dim c1 As New class1
Dim rs As ADODB.Recordset
c1.Reset
Set rs = CurrentProject.Connection.Execute("SELECT [id], [in], [out], f([id]) AS 余额 FROM test ORDER BY [id]")
Debug.Print c1.Elapsed
Set rs = Nothing
Set c1 = Nothing
End Function
Function t1()
Dim c1 As New class1
Dim rs As ADODB.Recordset
c1.Reset
Set rs = CurrentProject.Connection.Execute("SELECT [id], [in], [out], (SELECT SUM(b.[in]-b.[out]) AS bb FROM test b WHERE a.[id] <= b.[id]) AS ye FROM test a ORDER BY [id]")
Debug.Print c1.Elapsed
Set rs = Nothing
Set c1 = Nothing
End Function
(责任编辑：admin)
