sql - 统计变量中的查询结果
问题描述
我已经转换了一个txt文件来访问。
说我的访问查询会有
Data.Employeeid, Data.Location, Data.Hours, Location.Percent, Location.Union, Data.Date
现在对该数据集的查询将返回重复项。为了进一步处理数据,我希望 data.date 字段计数(每重复一个)。
我可以用一个变量来解决这个问题。
有人知道我如何让相同的选择查询计算重复的数量(基于employeeid)吗?
基本上,该查询中有 3 个连接,这让我很难过。
解决方案
您可以使用我的允许分组的RowNumber函数:
' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
ByVal Key As String, _
Optional ByVal GroupKey As String, _
Optional ByVal Reset As Boolean) _
As Long
' Uncommon character string to assemble GroupKey and Key as a compound key.
Const KeySeparator As String = "¤§¤"
' Expected error codes to accept.
Const CannotAddKey As Long = 457
Const CannotRemoveKey As Long = 5
Static Keys As New Collection
Static GroupKeys As New Collection
Dim Count As Long
Dim CompoundKey As String
On Error GoTo Err_RowNumber
If Reset = True Then
' Erase the collection of keys and group key counts.
Set Keys = Nothing
Set GroupKeys = Nothing
Else
' Create a compound key to uniquely identify GroupKey and its Key.
' Note: If GroupKey is not used, only one element will be added.
CompoundKey = GroupKey & KeySeparator & Key
Count = Keys(CompoundKey)
If Count = 0 Then
' This record has not been enumerated.
'
' Will either fail if the group key is new, leaving Count as zero,
' or retrieve the count of already enumerated records with this group key.
Count = GroupKeys(GroupKey) + 1
If Count > 0 Then
' The group key has been recorded.
' Remove it to allow it to be recreated holding the new count.
GroupKeys.Remove (GroupKey)
Else
' This record is the first having this group key.
' Thus, the count is 1.
Count = 1
End If
' (Re)create the group key item with the value of the count of keys.
GroupKeys.Add Count, GroupKey
End If
' Add the key and its enumeration.
' This will be:
' Using no group key: Relative to the full recordset.
' Using a group key: Relative to the group key.
' Will fail if the key already has been created.
Keys.Add Count, CompoundKey
End If
' Return the key value as this is the row counter.
RowNumber = Count
Exit_RowNumber:
Exit Function
Err_RowNumber:
Select Case Err
Case CannotAddKey
' Key is present, thus cannot be added again.
Resume Next
Case CannotRemoveKey
' GroupKey is not present, thus cannot be removed.
Resume Next
Case Else
' Some other error. Ignore.
Resume Exit_RowNumber
End Select
End Function
它在我的文章中有完整的记录:
此外,还包含一个可供下载的演示。
无需登录;只需找到链接:点击阅读全文。
推荐阅读
- rust - 如何为 Rust 中的引用创建“Iterable”特征?
- php - 我在正确编写此代码时遇到问题
- configuration - 如何在 HAProxy 中启用 HTTP/3 (QUIC)?
- sql - 确定错过的时间 - 找到时间差距
- json - Flutter在listview中显示json数组
- xml - XSLT 输出不如预期
- ios - ImageCropper.Forms 在 ios 平台上不工作 (System.MissingMethodException)
- sql-server - 来自链接服务器的另一个表的 INSERT 触发器很慢
- audio - VXML 音频文件播放状态
- arrays - 具有多个 CountIf 的动态数组